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

 

[Void_]

I would like to try and help but can you clarify these points?
The formula you are working on will go in the “Status” column, which is column C, correct?

If I understand correctly, there are 6 possible results that should be shown in the Status column:

1. Previous
   • Means there is a more recent entry? Please clarify the circumstances for Previous
2. Current
   • Means it (what is “it”?) is the most recent appearance of what? What is it being compared to?
3. Due
   • Sounds like Due means that the Service Due Date (column L, not shown in your image) is within 3 months of the current date?
4. Overdue
   • Sounds like the current date is beyond the Service Due Date (column L, not shown in your image)?
5. No Access
   • To be shown when column B says “No Access”
6. Building Site
   • To be shown when column B says “Building Site”

Can you please confirm/correct my assumptions and fill in the gaps?

 

[Bonnie Janes]

I would like to try and help but can you clarify these points?
The formula you are working on will go in the “Status” column, which is column C, correct?

If I understand correctly, there are 6 possible results that should be shown in the Status column:

1. Previous
   • Means there is a more recent entry? Please clarify the circumstances for Previous
2. Current
   • Means it (what is “it”?) is the most recent appearance of what? What is it being compared to?
3. Due
   • Sounds like Due means that the Service Due Date (column L, not shown in your image) is within 3 months of the current date?
4. Overdue
   • Sounds like the current date is beyond the Service Due Date (column L, not shown in your image)?
5. No Access
   • To be shown when column B says “No Access”
6. Building Site
   • To be shown when column B says “Building Site”

Can you please confirm/correct my assumptions and fill in the gaps?

Hi Void, yes you are bang on.

So the specifics of each row is a combination of location & discipline, I have a MAXIFs formula, that counts all the service dates for specific combo, and returns the highest date from that selection, and yeah, cell not shown.

Again, current, is most recent appearance of a combo of location & discipline.

Due means due within 3 months. Each discipline has a difference frequency, i.e. 6 months or quarterly or yearly etc. etc., this info is looked at, and generates a service due date, again column not shown. This is working fine.

It's overdue I'm struggling with, problem I'm having is if the service is completed slightly earlier than forecast, it's appearing as overdue, I'm not sure of the best way to configure it.

No Access / Building site, overrides everything - this works fine.

Thanks in advance for you help!! xx

 

[Bonnie Janes]

I would like to try and help but can you clarify these points?
The formula you are working on will go in the “Status” column, which is column C, correct?

If I understand correctly, there are 6 possible results that should be shown in the Status column:

1. Previous
   • Means there is a more recent entry? Please clarify the circumstances for Previous
2. Current
   • Means it (what is “it”?) is the most recent appearance of what? What is it being compared to?
3. Due
   • Sounds like Due means that the Service Due Date (column L, not shown in your image) is within 3 months of the current date?
4. Overdue
   • Sounds like the current date is beyond the Service Due Date (column L, not shown in your image)?
5. No Access
   • To be shown when column B says “No Access”
6. Building Site
   • To be shown when column B says “Building Site”

Can you please confirm/correct my assumptions and fill in the gaps?

Hi Void - I've created an example I could send you might make it easier... can't upload to here though, tried downloading the thing for a mini-sheet but it keeps saying it can't open with a protected spreadsheet, but it isn't protected, so I don't know... I could email to you if you like

 

[MARK858]

I am afraid that you shouldn't share files via email (please see rule 4 in the link Message Board Rules), but what you can do is...

If you can't use XL2BB upload your file to a free file hosting site like www.box.com or www.dropbox.com, mark the file for sharing and post the link it provides in the thread (or provide a link to the file in your Onedrive).


Make sure you sanitize any sensitive data before uploading the file

P.S.

keeps saying it can't open with a protected spreadsheet, but it isn't protected

Is it definitely saying that or is it saying that the Macro's are blocked? Can you post a snapshot of the error?

 

[Bonnie Janes]

I am afraid that you shouldn't share files via email (please see rule 4 in the link Message Board Rules), but what you can do is...

If you can't use XL2BB upload your file to a free file hosting site like www.box.com or www.dropbox.com, mark the file for sharing and post the link it provides in the thread (or provide a link to the file in your Onedrive).


Make sure you sanitize any sensitive data before uploading the file

P.S.

Is it definitely saying that or is it saying that the Macro's are blocked? Can you post a snapshot of the error?

Hi - this is the message I'm getting...

 

[Bonnie Janes]

I am afraid that you shouldn't share files via email (please see rule 4 in the link Message Board Rules), but what you can do is...

If you can't use XL2BB upload your file to a free file hosting site like www.box.com or www.dropbox.com, mark the file for sharing and post the link it provides in the thread (or provide a link to the file in your Onedrive).


Make sure you sanitize any sensitive data before uploading the file

P.S.

Is it definitely saying that or is it saying that the Macro's are blocked? Can you post a snapshot of the error?

I can't install dropbox as it's a work laptop, I need permission... I'm a bit stuck. Yes I've removed all sensitive data from the sample.

 

[MARK858]

See the article attached about Protected View and see the disabling part near the bottom but first if you right click the XL2BB addin, click Properties, and on the General tab do you see an unblock checkbox (it might not be there btw)?

Excel Protected View: How to Remove It (Once and for All)? - MiniTool

Why is Excel opening in Protected View? How to take off Protected View in Excel? How to turn off Protected View completely? Read and find answers here.

www.minitool.com

I'm out for a few hours now so will pick up on this when I get back if needed

 

[Void_]

Hi Void, yes you are bang on.

So the specifics of each row is a combination of location & discipline, I have a MAXIFs formula, that counts all the service dates for specific combo, and returns the highest date from that selection, and yeah, cell not shown.

Again, current, is most recent appearance of a combo of location & discipline.

Due means due within 3 months. Each discipline has a difference frequency, i.e. 6 months or quarterly or yearly etc. etc., this info is looked at, and generates a service due date, again column not shown. This is working fine.

It's overdue I'm struggling with, problem I'm having is if the service is completed slightly earlier than forecast, it's appearing as overdue, I'm not sure of the best way to configure it.

No Access / Building site, overrides everything - this works fine.

Thanks in advance for you help!! xx

Hi Bonnie,

I’m having a really difficult time conceptualizing the explanation. Let’s pick on Current for the moment. If I understand correctly, you want column C to show “Current” under certain conditions. I don’t have the context to understand what “most recent appearance of a combo of location & discipline” means. From your image, it looks like Discipline might be column D, but the section of your formula having to do with Current looks at P1 (current date), K (previous service date) and M (3 month date window). But I don’t know what “3 month date window” means either.

I have 2 and half questions.
First Question: Is the logic I provided for items 3 (Due), 4 (Overdue), 5 (No Access), and 6 (Building Site) accurate?
If so…
Second Question: Can you provide a more detailed explanation of 2 (Current)? I think understanding column M better might help with this.
Half question: Can you provide a more detailed explanation of 1 (Previous)?

I’m sorry I was not able to provide a quick solution.

 

[Bonnie Janes]

Hi Bonnie,

I’m having a really difficult time conceptualizing the explanation. Let’s pick on Current for the moment. If I understand correctly, you want column C to show “Current” under certain conditions. I don’t have the context to understand what “most recent appearance of a combo of location & discipline” means. From your image, it looks like Discipline might be column D, but the section of your formula having to do with Current looks at P1 (current date), K (previous service date) and M (3 month date window). But I don’t know what “3 month date window” means either.

I have 2 and half questions.
First Question: Is the logic I provided for items 3 (Due), 4 (Overdue), 5 (No Access), and 6 (Building Site) accurate?
If so…
Second Question: Can you provide a more detailed explanation of 2 (Current)? I think understanding column M better might help with this.
Half question: Can you provide a more detailed explanation of 1 (Previous)?

I’m sorry I was not able to provide a quick solution.

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