Comparing a date to today’s date?

[juzsp]

Right so my spreadsheet for planning and reporting on one of our work programmes is taking shape nicely (thanks to the help of you guys) but I have run into another problem.

At the moment I have a column (M) where the formula Returns the word “Overdue” if the date a job is “Closed” (column I) is after the date that the job is due to be done (column H)

here is the formula im currently using:

=IF(SUM(H152-I152)<0,"Overdue","")

It find the number of days difference between when the job is due to be done and when it was done, if it’s a negative figure then the job went overdue.

However working this way will only show me if the job went overdue if the job is closed. If a job was due to be completed by say 15th July but it is now the 17th July and the job is still not closed it won’t show as overdue.

One more thing. If we went to look at a job and couldn’t do it at that time then I put the word “inspected” in the “Closed” column (I)

Is there a way of adding to my formula so it looks at the “Closed” column (I) and if the word “inspected” appears or the cell is blank then it will use today’s date (not sure if you can add that sort of command) to compare with the “Due by” column (H)?

I have a few other questions about how to do other things with my spreadsheet, like how to average the difference between the numbers in 2 different columns but only if another column has certain data in it but im not sure if I should ask them in a separate thread or just post them here?

Thanks for all your help peeps. Your all making me look very clever at work!!!

[Waylander]

Hi juzsp

Any chance you could post an example of the spreadsheet, I can think of at least 2 ways to do what you want with the date comparisons but would like to see the layout ouf the sheets and data to choose the best one

Cheers

[juzsp]

Here is an example of what i have, the full spreadsheet is about 5-6mb so couldn't upload that.

Thanks for taking the time to look

[Waylander]

Hi Juzsp

Sorry I got taken ill, and haven't been around, thankfully back now and will look at your problem as no one else has picked it up

Again Apologies

[Waylander]

Right not too bad

Have attached a revised file with the command you were after, have displayed it on 2 sheets ,

Option 1 just shows the date calculation, in a seperate column with the status column revised to use this instead of the straight calc

Option 2 is probably the prettiest as I have included all of the stuff into 1 calculation, however if you are not used to excel, these nested IF statements can be a pain to unravel, I'll try and explain my reasoning below

Full function is

=IF(IF(C14="",B14-TODAY(),IF(ISERR(B14-C14),B14-TODAY(),B14-C14))>0,"","Overdue")

What I'm doing is using your original formula as the outer parenthesis (in Blue)

Then adding a new set of if statements the first one (in red) does the calculation for blank cells, also using the TODAY function to get todays date for the formula (warning make sure your system date is correct or this goes to pot)

And then (in Green), I use the ISERR function to recognise when you have a #VALUE error and again calculate the formula using todays date

Hope this is what you are after, any questions give me a shout