SIGN UP

EDATE in Excel - Formula challenge!

Avatar Image
funkylad20 | 12:40 Fri 13th Nov 2009 | Computers
6 Answers
I have a date when a project has to submit a final report by, which is trigegred byt he project end date plus one month (using EDATE).

Anyone know a formula that can tell me, when I open the spreadsheet, if the report is overdue?
All I need it to recognise is the date today (each day, i.e. EDATE NOW) and if it is after the date the report is due, resulting in a big red 'OVERDUE' text entry in the cell?

Answers

1 to 6 of 6rss feed

Best Answer

No best answer has yet been selected by funkylad20. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Say your project end date+1 month is in the cell E1. In another cell where you want the Overdue indicator to be displayed, set the formual to:-

=IF(TODAY()>E1,"OVERDUE"," ")

If you want to make the OVERDUE text show as red, click in the cell, and select the Format menu and then Conditional Formatting.

In the 3 boxes, set the selections to 'Cell Value Is', 'Equal To' and in the final box ="OVERDUE".
Then select the font or cell colour by clicking the Format... button.
Question Author
Thanks...

So, how would I make it say 'OVERDUE' if it is, and 'NOT DUE' if it isn't?

And...the TODAY date, that is the date when the spreadsheet is opened, isn't it, and not when the formula is entered? i.e. if I looked in 3 months, the TODAY date would change?
Question Author
OK, I sussed it out...thank you
=IF(NOW()>Y3000,"OVERDUE","Not Due Yet")

ONE final thing, how would I make the cell blank if no entry is made? i.e. if not return is needed?
using same example as TTG it would be:-

=IF(TODAY()>E1,"OVERDUE","NOT DUE ")

you can then also use conditional formatting to change the cell colour depending on if it says overdue or not due in it (look on the formatting menu)

and yes, today will always the the current day, if you looked at it in 3 months it would still use the correct date.
Sorry misunderstood your request. Say you want to compare two different dates in two different cells (one date in A1 and the other in E1). The formula would read:-

=IF(A1>E1,"OVERDUE","OK")

This will show OVERDUE if A1 is greater than E1 otherwise, it will show OK.
for no entry if e1 is blank use

=IF(E1="","",IF(TODAY()>E1,"OVERDUE","NOT DUE "))

1 to 6 of 6rss feed

Do you know the answer?

EDATE in Excel - Formula challenge!

Answer Question >>

Related Questions

Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.