Hi all, Below mentioned formula is not functioning in 2023 in my tracker, but when I change the system date to 2022 then it works, please support to fix this error. =IF(MONTH(AB38)=MONTH(TODAY()-1),1,IF(MONTH(AO38)=MONTH(TODAY()-1),1,IF(MONTH(AL38)=MONTH(TODAY()-1),1,0))) Thank you in advance.

Nushad What do you want the formula to do? It currently returns 1 if at least one of the dates in AB38, AO38 and AL38 is in the same month as yesterday, 0 otherwise.

HansVogelaar Thank you for the reply, as you mentioned, formula has to return to 1 if at least one of the dates change to current month in the following sells AO38 and AL38, otherwise it has to remain 0.Below clarification is for your further reference. Order submitted date will be mentioned in the following sell AB38Appointment date will be mentioned in the following sell AO38Order completed date will be mentioned in the following sell AL38If the order submitted in the last week of the month and if the appointment booked for the 1st week of the next month then this record has to be filtered if I select 1.If I am generating the report for all the orders which have been completed for this month, it has to include the order submissions from this month as well as from the previous month. It was functioning well, but starting from 1st January 2023 it is not functioning.Below is the formula from the beginning of the row, please help on the below formula.=IF(MONTH(AB2)=MONTH(TODAY()-1),1,IF(MONTH(AO2)=MONTH(TODAY()-1),1,IF(MONTH(AL2)=MONTH(TODAY()-1),1,0)))

Nushad If one of the cells is empty, Excel will return 1 for the month, which is January. Try this version: =IF(OR(AND(AB2<>"",MONTH(AB2)=MONTH(TODAY()-1)),AND(AO2<>"",MONTH(AO2)=MONTH(TODAY()-1)),AND(AL2<>"",MONTH(AL2)=MONTH(TODAY()-1))),1,0)

Thank you for the reply, it doesn't functioning properly.When I am generating the report for the current month by selecting 1, it still filtering the submissions which are from previous month but were rejected, means appointment was not booked or order was not completed so the sell AO2 and sell AL2 are empty, so these has to be excluded.What I exactly want is, if I select 1, carry forwarded submissions from previous month to this month has to be included on top current month submissions.Submissions are from previous month, and if the appointment booked and if a date given in cell AO2 for this month are considered carry forwarded submissions.Below formula was working fine but, for this year 2023 not functioning, if you could add to include 2023 for the same formula would be better, thank you

Nushad Your formula has MONTH(TODAY()-1). That is the month number of TODAY()-1, i.e. yesterday. Did you perhaps want the month number of last month instead of the month number of yesterday?

Formula is not functioning | Microsoft Community Hub

12 Replies

johnli475
Copper Contributor
Jan 05, 2023
The most common reason for an Excel formula not calculating is that you have inadvertently activated the Show Formulas mode in a worksheet. To get the formula to display the calculated result, just turn off the Show Formulas mode by doing one of the following: Pressing the Ctrl + ` shortcut, or.
Regards: [hyperlink removed by admin]
HansVogelaar
MVP
Jan 05, 2023
Nushad

What do you want the formula to do? It currently returns 1 if at least one of the dates in AB38, AO38 and AL38 is in the same month as yesterday, 0 otherwise.
- Nushad
  Copper Contributor
  Jan 05, 2023
  HansVogelaar
  Thank you for the reply, as you mentioned, formula has to return to 1 if at least one of the dates change to current month in the following sells AO38 and AL38, otherwise it has to remain 0.
  
  Below clarification is for your further reference.
  
  Order submitted date will be mentioned in the following sell AB38
  Appointment date will be mentioned in the following sell AO38
  Order completed date will be mentioned in the following sell AL38
  
  If the order submitted in the last week of the month and if the appointment booked for the 1st week of the next month then this record has to be filtered if I select 1.
  
  If I am generating the report for all the orders which have been completed for this month, it has to include the order submissions from this month as well as from the previous month.
  
  It was functioning well, but starting from 1st January 2023 it is not functioning.
  
  Below is the formula from the beginning of the row, please help on the below formula.
  
  =IF(MONTH(AB2)=MONTH(TODAY()-1),1,IF(MONTH(AO2)=MONTH(TODAY()-1),1,IF(MONTH(AL2)=MONTH(TODAY()-1),1,0)))
  - HansVogelaar
    MVP
    Jan 05, 2023
    Nushad
    
    If one of the cells is empty, Excel will return 1 for the month, which is January. Try this version:
    
    =IF(OR(AND(AB2<>"",MONTH(AB2)=MONTH(TODAY()-1)),AND(AO2<>"",MONTH(AO2)=MONTH(TODAY()-1)),AND(AL2<>"",MONTH(AL2)=MONTH(TODAY()-1))),1,0)

Forum Discussion

Formula is not functioning

12 Replies

Resources