Showing overdue days based upon the data in two other fields

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
320
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi can anyone help me with the correct formula I need to show the number of days overdue based on data in two other fields.


Field 1 [Date Reqired for Compliance] in which the user inputs the date when someone needs to have responded to a request.

Field 2 [Date Material received] the user inputs the date when items have been received.

Field 3 [Status] in this field I would a formula which will show 'Complete' if Field 2 has a date entered or, if it is blank then display 'Awaits' if the date in Field 1 has not been reached (based on todays date) or show the number of days overdue (like this "23 days overdue").

Thanks any help appreciated
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try: =IF(Date_Recd<>"","Complete",IF(Date_Reqd>TODAY(),"Awaits",TODAY()-Date_Reqd&" days overdue"))
 
Upvote 0
Hi WaterGypsy,

Thank you for coming to my aid again Can I just clarify that the forumla is suitable for ACCESS.

I have tried your formula but Access doesn't like it.

I tried this =IIf([Date MaterialReceived]<>"","Complete",IIf([Date Required ForCompliance>TODAY()-Date Required For Compliance&" daysoverdue"))]

and is


=IF(Date MaterialReceived<>"","Complete",IF(Date Required ForCompliance>TODAY()-Date Required For Compliance&" daysoverdue"))

Niether worked, although the following works, however it doesn't do what I want.

=DateDiff("d",Date(),[Date Required ForCompliance])

Can you assist further?

Thanks Paul








 
Upvote 0
You shouldn't save this as a calculated field in a table, in a query or on a form the following should work:

Code:
(IIf(Nz([Date Material received])>0,"Complete",IIf([Date Reqired for Compliance]>=Date(),"Awaits",Date()-[Date Reqired for Compliance] & " Days Overdue")))

I have included the spelling mistake 'Reqired' in your field names as wasn't sure if it was deliberate or not.
 
Upvote 0
Sorry ... I assumed Excel and Access would be similar enough in this case ... I really shouldn't make that assumption :(

I don't have Access here -I'll have a look when I get home
 
Upvote 0
Hi stumac,

Thank you very much, this worked perfectly. Yes thank you also for pointing out my typo, I amended you formula accordingly.

Greatly appreciated.
 
Upvote 0
Hi WaterGypsy,

Thank you for trying to assist, much appreciated. Stumac has resolved my question. But thank you for trying.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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