Date Calculation for Number of Days Overdue

When you say "todays date in column B" is this a manually entered date, that will (could) be different for each row, or is it actually today's date? (not that it really matters, just curious - if it is =TODAY(), I suggest you put it in 1 cell and then just reference it, rather than having a while column of =TODAY() )

The formula should be pretty simple. Something like...
=if(A2<B2,B2-A2,A2-B2)
or you could get a bit more fancy with something like this...
=if(A2<B2,B2-A2&" Days Overdue",A2-B2&" Days until Overdue")

Originally Posted by FDibbins

When you say "todays date in column B" is this a manually entered date, that will (could) be different for each row, or is it actually today's date? (not that it really matters, just curious - if it is =TODAY(), I suggest you put it in 1 cell and then just reference it, rather than having a while column of =TODAY() )

The formula should be pretty simple. Something like...
=if(A2<B2,B2-A2,A2-B2)
or you could get a bit more fancy with something like this...
=if(A2<B2,B2-A2&" Days Overdue",A2-B2&" Days until Overdue")

Very good idea which will avoid much of the volatility of the TODAY() function

Originally Posted by Pepe Le Mokko

......which will avoid much of the volatility of the TODAY() function

Not according to Charles Williams (Decision Models) - on his site he describes how volatile functions work. This is one part

Direct dependents of volatile functions are always recalculated:
If A1 contains =NOW() and A2 contains =A1 and A3 contains =A2 then both A2 and A3 will be recalculated at each recalculation.

see the link

Originally Posted by Sharr76

Dabooka that is what I am looking for, how can I add a format now to change the over due day numbers to RED

Conditonal formatting is your friend there

Highlight your column (if you want it down the entire column otherwise just the cells), select Conditional Formatting, select New Rule

- "Format only cells that contain"
- Format only cells with : Cell value / greater than / 0 (that assumes you only want red if it is a day over)
- Set format (here choose your text style and colour)
- Click okay

That should do it. The top two text cells will then appear red, just highlight those and delete the rule.

Bear in mind at the minute you have it set up so the countdown is a negative value and when it's overdue it becomes a positive number. Personally I would change the cell title to "Days Until Overdue" and then swap the formula I suggested earlier around to =SUM(L3-$M$1). That will then read positive value until day zero, then negative aftwards once it's O/D

To me it just looks more obvious that the numbers are then overdue, what with a - sign and in red.

HTH

Originally Posted by Dabooka

=SUM($M$1-L3)

in M3 shows days remaining; is that what you mean?

BTW the SUM function is unnecessary

Originally Posted by jo83au

How do you change this to whole days? everything I do does not work.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread