Stop a date from updating each successive day

[eyecare]

This is the function I am using,

IF(C1=0, "", TODAY())

This works, however as you can see the resulting date will change. I would like the date to be fixed and not change.

Would appreciate your help

............... Thanks, Gene Gould OD

 

[Andrew Poulsom]

Excel and bl**dy UK/European dates!

It's because today minus 6 is 12th October. In dd/mm format that's 12/10 but Excel gets confused about what is the day and what is the month and returns 10th December.

I've tried lots of things and the only solution I have found is:

Sub Test()
    Range("G1").Value = Date - Range("G2").Value
    Range("G1").NumberFormat = "dd/mm/yyyy"
End Sub

This is not good, because one should be able to use the named format "Short Date" to ensure international compatibility. That's why it exists.

 

[shades]

Thanks, Andrew - yours is considerably shorter.

I'm learning this hit-or-miss, so this is a definite hit!

 

[Chas17]

If you just want a date in a cell not to change, use keyboard shortcut
"CNTRL-semicolon"
"CNTRL-Colon" is time.

To place in cell last time sheet saved, place in vb code under workbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("update") = "Updated on " & Date & " " & Time
Range("A5").Value = "LAST UPDATED: " & Now
End Sub

name a cell "update"

 

[B_2]

On 2002-10-18 05:19, Andrew Poulsom wrote:
Excel and bl**dy UK/European dates!

It's because today minus 6 is 12th October. In dd/mm format that's 12/10 but Excel gets confused about what is the day and what is the month and returns 10th December.

I've tried lots of things and the only solution I have found is:

Sub Test()
    Range("G1").Value = Date - Range("G2").Value
    Range("G1").NumberFormat = "dd/mm/yyyy"
End Sub

This is not good, because one should be able to use the named format "Short Date" to ensure international compatibility. That's why it exists.

Just got back from UNI, much appreciated Andy