Jan 05 2023 12:26 AM
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.
Jan 05 2023 02:01 AM
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.
Jan 05 2023 02:45 AM
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)))
Jan 05 2023 03:06 AM
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)
Jan 05 2023 04:47 AM
Jan 05 2023 05:16 AM
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?
Jan 05 2023 05:45 AM
Jan 05 2023 05:54 AM
You keep on mentioning that it doesn't work in 2023, but you haven't told us what the actual problem is.
Jan 05 2023 05:58 AM
Jan 05 2023 06:13 AM
Jan 05 2023 07:45 AM
I'm afraid I don't understand. Could you provide some concrete examples? Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Jan 05 2023 07:50 AM - last edited on Jun 03 2023 04:19 PM by Meenah_Khosraw
Jan 05 2023 07:50 AM - last edited on Jun 03 2023 04:19 PM by Meenah_Khosraw
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]
Jan 05 2023 08:53 AM
@Hans Vogelaar Thank you for your reply, please allow me sometimes I will update you.