PDA

View Full Version : [SOLVED] Confused between Date and Today



Joinersfolly
04-26-2016, 07:46 AM
In my code I used the bolded line below which seemed to work fine, however I found to my horror that each day I opened my workbook
all the dates changed to reflect whatever the date is today not what date 'Today' was the day i entered the code ie.

if on the 16/04/2016 i used "=Today()-" & Varname
16/04/2016-3 I got 13/04/2016 which is what i wanted.
However when i opened the sheet again on 20/04/2016 the cell result had changed to 20/04/2016-3 =17/04/2016

Once the date has been entered I do not want it to change again.

Will the change from the underlined code to the row below it work?

Regards



Dim LastRow As Long'Dim Varname As Integer

Dim Unchanging As Variant
Unchanging = Date
'MsgBox Unchanging
Dim Varname As Variant






If Target.Address = "$D$1" Then
Varname = Range("D1")





'Range("A" & LastRow).Formula = "=Today()-" & Varname

Range("A" & LastRow).Value = Unchanging - Varname

Zack Barresse
04-26-2016, 09:39 AM
There's nothing you can do with the TODAY() function to make it return anything but the system date on recalculation, and since it's a volatile function it will update at any calc chain trigger anywhere.

To get around this, set the Value property instead of using the Formula property. You can either use the VBA.Date function, or use the Evaluate method to return the TODAY() equivalent. So it would look something like this...

Range("A" & LastRow).Value = VBA.Date - Varname

To specifically answer your question, this will simplify your underlined code (which doesn't actually show as underlined when you wrap it in CODE tags).

Joinersfolly
04-26-2016, 11:50 AM
Thanks for your help Zack.