IF CONDITION W BLANK DATE BETWEEN 2 DATES

R

RD1982

New Member
Joined
Nov 10, 2022
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Could you please help me to find the correct formula to identify when an order is overdue based on planned delivery date?
=IF(X2<=U2,"Not Due", IF(X2>U2,"Overdue",IF(OR(X2="Blank",U2>TODAY()),"Investigate","")))

1668058587993.png


For instance, if I have past due planned delivery date but my GR column is blank I should see results as "overdue". I'm using the below formula which is not capturing that... please help!!!
DOES NOT WORK FOR THE BELOW BLANK DATES AS PLANNED DELIVERY DATE IS PAST DUE SHOULD BRING ME THE "INVESTIGATE" OPTION.

1668058651845.png
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Book1
UXAO
1Planned Delv DateGR Date (Rreceived into Plant)STO Overdue
26/10/202214/10/2022Overdue
36/10/20226/10/2022Not Due
46/10/20226/10/2022Not Due
56/10/20227/10/2022Overdue
66/10/20226/10/2022Not Due
76/10/20226/10/2022Not Due
86/10/20227/10/2022Overdue
96/10/20226/10/2022Not Due
106/10/20226/10/2022Not Due
116/10/20226/10/2022Not Due
1228/10/2022Investigate
1321/10/2022Investigate
1428/10/2022Investigate
1524/10/2022Investigate
1630/10/2022Investigate
1730/10/2022Investigate
1830/10/2022Investigate
Sheet1
Cell Formulas
RangeFormula
AO2:AO18AO2=IF(OR(X2="",U2>TODAY()),"Investigate",IF(X2<=U2,"Not Due","Overdue"))
 
Upvote 0
kevin9999 said:
Book1
UXAO
1Planned Delv DateGR Date (Rreceived into Plant)STO Overdue
26/10/202214/10/2022Overdue
36/10/20226/10/2022Not Due
46/10/20226/10/2022Not Due
56/10/20227/10/2022Overdue
66/10/20226/10/2022Not Due
76/10/20226/10/2022Not Due
86/10/20227/10/2022Overdue
96/10/20226/10/2022Not Due
106/10/20226/10/2022Not Due
116/10/20226/10/2022Not Due
1228/10/2022Investigate
1321/10/2022Investigate
1428/10/2022Investigate
1524/10/2022Investigate
1630/10/2022Investigate
1730/10/2022Investigate
1830/10/2022Investigate
Sheet1
Cell Formulas
RangeFormula
AO2:AO18AO2=IF(OR(X2="",U2>TODAY()),"Investigate",IF(X2<=U2,"Not Due","Overdue"))
Click to expand...
Thank you very much @kevin9999 I believe I was overlooking the formula - much clear after your guidance. However, how would you tag as "Ok" for those which are not past due yet per below? example?
1668128311315.png
 
Upvote 0
Do you mean like this (planned date is today or in the future)

test.xlsm
UXAO
1Planned Delv DateGR Date (Received into Plant)STO Overdue
26/10/202214/10/2022Not Due
36/10/20226/10/2022Not Due
46/10/20226/10/2022Not Due
56/10/20227/10/2022Not Due
66/10/20226/10/2022Not Due
76/10/20226/10/2022Not Due
86/10/20227/10/2022Not Due
96/10/20226/10/2022Not Due
106/10/20226/10/2022Not Due
116/10/20226/10/2022Not Due
1228/10/2022Investigate
1321/10/2022Investigate
1428/10/2022Investigate
1524/10/2022Investigate
1630/10/2022Investigate
1730/10/2022Investigate
1830/10/2022Investigate
1915/11/2022OK
2016/11/2022OK
2117/11/2022OK
2218/11/2022OK
2319/11/2022OK
2420/11/2022OK
25
Sheet2
Cell Formulas
RangeFormula
AO2:AO24AO2=IF(U2>=TODAY(),"OK",IF(OR(X2="",X2>TODAY()),"Investigate",IF(U2<=X2,"Not Due","Overdue")))
 
Upvote 0
kevin9999 said:
Do you mean like this (planned date is today or in the future)

test.xlsm
UXAO
1Planned Delv DateGR Date (Received into Plant)STO Overdue
26/10/202214/10/2022Not Due
36/10/20226/10/2022Not Due
46/10/20226/10/2022Not Due
56/10/20227/10/2022Not Due
66/10/20226/10/2022Not Due
76/10/20226/10/2022Not Due
86/10/20227/10/2022Not Due
96/10/20226/10/2022Not Due
106/10/20226/10/2022Not Due
116/10/20226/10/2022Not Due
1228/10/2022Investigate
1321/10/2022Investigate
1428/10/2022Investigate
1524/10/2022Investigate
1630/10/2022Investigate
1730/10/2022Investigate
1830/10/2022Investigate
1915/11/2022OK
2016/11/2022OK
2117/11/2022OK
2218/11/2022OK
2319/11/2022OK
2420/11/2022OK
25
Sheet2
Cell Formulas
RangeFormula
AO2:AO24AO2=IF(U2>=TODAY(),"OK",IF(OR(X2="",X2>TODAY()),"Investigate",IF(U2<=X2,"Not Due","Overdue")))
Click to expand...
@kevin9999 BRILLIANT!!!!! Thank you very much! Just changed the last sign from < to > :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Urgent Excel IF formula help!!
Replies
6
Views
349
Sufiyan97
Sufiyan97
Z
  • Solved
Conditional Formatting (CF Rule) Using INDIRECT Function
Replies
5
Views
263
Fluff
Fluff
R
  • Locked
  • Question
Excel formula - Overdue orders with dates
Replies
1
Views
502
RD1982
R
N
  • Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
150
Alex Blakenburg
A
M
  • Question
Highlight row based on first date found within cell
Replies
6
Views
399
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,214,589
Messages
6,120,415
Members
448,960
Latest member
AKSMITH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top