Worksheet cell date advice

[ipbr21054]

Hi.
In cell L13 i used to have =TODAY() but just noticed that somehow its no longer present.
Because of this the last 8 invoice have the dat 14/02/2023

I am looking to apply the date to the L13 cell using VBA so this dont happen again.

Would i be correct to write the code like this,pleae advise.

Range("L13").NumberFormat = "mm/dd/yyyy"

On the worksheet in question i dont know which sub to put it in.

Please advise the above two questions.

Thanks

 

[offthelip]

put this in the worksheet code for the sheet where you want it ( right click on the tab and select view code )

Private Sub Worksheet_Activate()
Range("L13") = Now()
Range("L13").NumberFormat = "mm/dd/yyyy"

End Sub

 

[awoohaw]

There is also a keyboard shortcut that you can input date quickly: CTRL+: (Control - Semicolon).

 

[Joe4]

The TODAY() function ALWAYS returns the current date. So every day you open any workbook that uses it, it will show the current date.
If you want to capture the date in VBA, you can assign it like this:

Range("L13").Value = Date

EDIT: Regarding the first reply above, just be aware that Date and Now are do NOT the return the same values in VBA.
Now will always return a time component too, and Date will not.
Even if you format a value set using the Now function to use a date format of "m/dd/yyyy", it will still retain the time piece.
That could be important if you are using that date field in calculations. If you are checking to see if two dates are equal, they may appear to be, but Excel may say there are not because of different time components.

 

[ipbr21054]

put this in the worksheet code for the sheet where you want it ( right click on the tab and select view code )

Private Sub Worksheet_Activate()
Range("L13") = Now()
Range("L13").NumberFormat = "mm/dd/yyyy"

End Sub

Thanks very much