Microsoft 365 Excel IF/Due/Overdue Formula Problem

[Bonnie Janes]

Hi guys, looking to you for help with my complicated IF/AND/OR formula. I don't seem to be able to get it it work quite how I want.

I need a cell that looks at dates and returns whether a line is, previous, current, due or overdue. Previous means there is a more recent entry, current means it's the most recent appearance, due means due within 3 months and overdue means overdue. I think the complication is occurring because the services have been done early, due in June but completed in May.

There are also a couple of overwrites "no access" and "building site" which take priority.

Where am I going wrong?

$P$1 is the current date
L28 is service due date
K28 is previous service date
M28 shows 3 month due date window (this is working fine)

=IFERROR(IF(B28="No Access","No Access",IF(B28="Building Site","Building Site",IF(AND($P$1>=L28,$P$1<=K28),"Overdue",IF(AND($P$1>=M28,$P$1<=L28),"Due",IF(AND($P$1>=K28,$P$1<=M28),"Current","Previous"))))),"")

Type	Status	Discipline	Service Date	Status should be showing :
Service	Overdue	Fire Alarm	09/02/2022	Previous
Service	Overdue	Fire Alarm	11/02/2022	Previous
Service	Overdue	Fire Alarm	11/05/2022	Previous
Service	Overdue	Fire Alarm	25/05/2022	Previous
Service	Overdue	Fire Alarm	08/06/2022	Previous
Service	Overdue	Fire Alarm	31/10/2022	Previous
Service	Overdue	Fire Alarm	04/11/2022	Previous
Service	Overdue	Fire Alarm	09/12/2022	Previous
Service	Overdue	Fire Alarm	09/12/2022	Previous
Service	Overdue	Fire Alarm	09/12/2022	Previous
Service	Due	Fire Alarm	17/02/2023	Due - correct
Service	Previous	Fire Alarm	09/05/2023	Current
Service	Previous	Fire Alarm	10/05/2023	Current
Service	Previous	Fire Alarm	12/05/2023	Current
Service	Previous	Fire Alarm	19/05/2023	Current
No Access	No Access	Fire Alarm	16/06/2023	No Access - correct

Apologies if I've explained this badly, please ask for any clarification.

Thanks in advance for your help.

xx

 

[Bonnie Janes]

Don't apologise, god, thanks for your help - I apologise for not being clearer!! And sorry for slow response, I'm on and off line atm.

I think the overdue one is the main problem, because the "previous" service date is maybe confusing things, I think I'm approaching this in the wrong way, there must be a better way. I've pasted below a sample of each line and each formula. The due one (service due within 3 month) is working fine, previous is working fine and current is working fine, I think I need to maybe add another column date for overdue to look at, but I can't get my head round how to it!! Suggestions welcome. I think I might be overcomplicating things, it's been known!

Type	Status	Discipline	Location 2	Service Date	Previous	Service Due	-3	Frequency	Freq Code	13/06/2023​
Service	Current	Ex	PH	09/12/2022	09/12/2022	09/12/2023	09/09/2023	Yearly	12
Testing	Previous	Sp	PH	15/12/2022	15/12/2022	15/12/2022	15/09/2022	Weekly	0.4
Service	Due	As	WOP	16/12/2022	16/12/2022	16/06/2023	16/03/2023	Six Monthly	6
Service	Previous	Ex	WOP	01/04/2022​	01/04/2022	01/04/2023	01/01/2023	Yearly	12

Type	Status	Discipline	Location 2	Service Date	Previous	Service Due	-3	Frequency	Freq Code	=TODAY()
Service	=IFERROR(IF(A2="No Access","No Access",IF(A2="Building Site","Building Site",IF(AND($K$1>=G2,$K$1<=F2),"Overdue",IF(AND($K$1>=H2,$K$1<=G2),"Due",IF(AND($K$1>=F2,$K$1<=H2),"Current","Previous"))))),"")	Ex	PH	44904	=IFERROR(MAXIFS($E$2:F1000,$D$2:E1000,D2,$C$2:D1000,C2),"")	=IFERROR(EDATE(E2,J2),"")	=IFERROR(EDATE(G2,$H$1),"")	=IFERROR(IF(A2="Testing","Weekly",(VLOOKUP(C2,Sheet2!$A$1:$C$16,3,FALSE))),"")	=IF(I2="Weekly",0.4,IF(I2="Monthly",1,IF(I2="Six Monthly",6,IF(I2="Quarterly",4,IF(I2="Yearly",12,IF(I2="Periodic",0,""))))))
Testing	=IFERROR(IF(A3="No Access","No Access",IF(A3="Building Site","Building Site",IF(AND($K$1>=G3,$K$1<=F3),"Overdue",IF(AND($K$1>=H3,$K$1<=G3),"Due",IF(AND($K$1>=F3,$K$1<=H3),"Current","Previous"))))),"")	Sp	PH	44910	=IFERROR(MAXIFS($E$2:F1001,$D$2:E1001,D3,$C$2:D1001,C3),"")	=IFERROR(EDATE(E3,J3),"")	=IFERROR(EDATE(G3,$H$1),"")	=IFERROR(IF(A3="Testing","Weekly",(VLOOKUP(C3,Sheet2!$A$1:$C$16,3,FALSE))),"")	=IF(I3="Weekly",0.4,IF(I3="Monthly",1,IF(I3="Six Monthly",6,IF(I3="Quarterly",4,IF(I3="Yearly",12,IF(I3="Periodic",0,""))))))
Service	=IFERROR(IF(A4="No Access","No Access",IF(A4="Building Site","Building Site",IF(AND($K$1>=G4,$K$1<=F4),"Overdue",IF(AND($K$1>=H4,$K$1<=G4),"Due",IF(AND($K$1>=F4,$K$1<=H4),"Current","Previous"))))),"")	As	WOP	44911	=IFERROR(MAXIFS($E$2:F1002,$D$2:E1002,D4,$C$2:D1002,C4),"")	=IFERROR(EDATE(E4,J4),"")	=IFERROR(EDATE(G4,$H$1),"")	=IFERROR(IF(A4="Testing","Weekly",(VLOOKUP(C4,Sheet2!$A$1:$C$16,3,FALSE))),"")	=IF(I4="Weekly",0.4,IF(I4="Monthly",1,IF(I4="Six Monthly",6,IF(I4="Quarterly",4,IF(I4="Yearly",12,IF(I4="Periodic",0,""))))))
Service	=IFERROR(IF(A5="No Access","No Access",IF(A5="Building Site","Building Site",IF(AND($K$1>=G5,$K$1<=F5),"Overdue",IF(AND($K$1>=H5,$K$1<=G5),"Due",IF(AND($K$1>=F5,$K$1<=H5),"Current","Previous"))))),"")	Ex	WOP	44652	=IFERROR(MAXIFS($E$2:E1003,$D$2:D1003,D5,$C$2:C1003,C5),"")	=IFERROR(EDATE(E5,J5),"")	=IFERROR(EDATE(G5,$H$1),"")	=IFERROR(IF(A5="Testing","Weekly",(VLOOKUP(C5,Sheet2!$A$1:$C$16,3,FALSE))),"")	=IF(I5="Weekly",0.4,IF(I5="Monthly",1,IF(I5="Six Monthly",6,IF(I5="Quarterly",4,IF(I5="Yearly",12,IF(I5="Periodic",0,""))))))

So the last row should be showing overdue, but I think what is happening, is the formula for previous is taking precedence. Does that make sense? How can I get round this?

* But in answer to your questions, the spreadsheet is a list of services, carried out on a certain date, for difference disciplines in different locations, and there are varying combinations, for example one location might have 5 disciplines, and vice versa, that's what defines it, so lights could have been serviced in one building, but be overdue in another building. This is what the MAXIFS looks at, to generate the previous highest service date, to know which dates to include to find the highest entry of.

You can see all relevant columns now.

Don't worry about no access and building site, that is an overwrite and is working ok.

Excuse me if my explanations aren't clear, I'm neurodiverse and often struggle with communication

Do I maybe need to add a column that looks up and MARKS in some way the MOST RECENT SERVICE, could the overdue then look to that mark, that changes, as new service sheets get added. That might be a way round it, cos there's no distinction between current and previous really is there, the dates appear same in service date column and previous column. This could be the problem. What do you think?

If you know how I could do that - add a formula that looks up the combo, like the MAXIF does, and marks the most recent service and previous service, in that combo, as different somehow?

 

[Void_]

Here is my understanding of the relevant columns:

• Status:
  • The column we are solving for
• Service Date:
  • When service of this particular discipline was performed at this location
• Previous:
  • Determines the most recent date that service of this discipline was performed at this location
• Service Due:
  • The date that the next service is due
• -3:
  • The date three months prior to Service Due

Each possible Status:

1. Previous
   • Should display when the Service Date for this row is not the most recent for this combination of discipline and location. I think this condition should come before
2. Current
   • The opposite of Previous. Should display when the Service Date for this row is the most recent for this combination of discipline and location
3. Due
   • Should display when the Previous column is within three months of the Service Due column. The effect being that all instances of this type of row would show “Due” if the most recent Service Date of this type of row is within three months of Service Due.
4. Overdue
   • Should display when the date in the Service Due column has passed.
5. No Access
   • Previously discussed
6. Building Site
   • Previously discussed

If I have that correct, I think the formula in the Status column could be modified like this:
IF(A2="No Access","No Access",
IF(A2="Building Site","Building Site",
IF(Today>=Service Due column,”Overdue”
IF(Today>=-3 column,"Due",
IF(Service Date column=Previous column,"Current",
"Previous")))))

The end formula might look something like:

=IF(B711="No Access","No Access",IF(B711="Building Site","Building Site",IF(B711="Testing","N/A Test",IF(G711="","No Date",IF(AND($Q$1>=M711,G711>=H711),"Overdue",IF(AND($Q$1>=N711,$Q$1<=M711,G711>=H711),"Due",IF(G711=H711,"Current","Previous")))))))