Solved Excel: Substitute a past date with the current date

January 27, 2016 at 03:59:17
Specs: Windows 7
Hello. I have a cell on my spread sheet which calculates an earliest date due. The cell references other cells to achieve this and looks like this '=C51+(F8*7)' where cell C51 is a date field in the format DD/MM/CCYY and contains the date 26/12/2015. And cell F8 is a number field which contains the value '4'. This value '4' is indicating a number of weeks. So the calculation (F8*7) gets me the number of days and I then add those days to the date in cell C51 to get 23/01/2016.

I want this cell to output the current date if the calculation above results in a date that is in the past. Can anyone tell me if there is a simple way to achieve this? Thank you.


See More: Excel: Substitute a past date with the current date

Report •

#1
January 27, 2016 at 06:14:57
✔ Best Answer
If I understand you correctly, this should get you what you want:

=IF("your calculation"< TODAY(),TODAY(),"your calculation")

However, the TODAY() will update every day to always show the current date, assuming "your calculation" results in a date that is in the past.

In other words, it will not lock in the first date it calculates. It will always display "today's" date.

Is that what you want to see?

One other issue: As explained at the link below, the TODAY() function is "volatile". That means that is will recalculate every time the sheet calculates, even if none of the arguments have changed. A large number of volatile functions can slow a worksheet down.

https://msdn.microsoft.com/en-us/li...

Volatile and Non-Volatile Functions

Excel supports the concept of a volatile function, that is, 
one whose value cannot be assumed to be the same from one 
moment to the next even if none of its arguments (if it 
takes any) has changed. Excel reevaluates cells that contain 
volatile functions, together with all dependents, every time 
that it recalculates. For this reason, too much reliance on 
volatile functions can make recalculation times slow. Use them
sparingly.

The following Excel functions are volatile:

NOW
TODAY
RAND
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 27, 2016 at 06:30:22
BTW...If your requirement is to lock in "today's" date on the day it is first calculated, you will probably need to use a macro. A macro could put "today's" date in the cell as a hard coded value which would never change.

Some more specifics as to your overall requirements might help lead us to a more precise solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
January 27, 2016 at 06:50:28
Hello DerbyDad03. Yes, what you gave me worked for me. I'm aware that the TODAY() function will recalculate every time the sheet calculates and this is fine for my purposes. There's shouldn't be too many of these present on my sheet when it's finished. I estimate < 50. Thank you.

Report •
Related Solutions


Ask Question