Using date & Time in Excel

[lamaar]

Hi all,

I have a formula where by if a cell is more than 5 then it inserts teh date and time (using now)

However, if the data in one cell changes, it changes the date and time in all cells which are referenced with NOW. I know this is a feature but I need to insert a static date and time. I tried with today etc but still no joy. Someone please put my mind at rest.

Thanks Lamaar75@hotmail.com

 

[xlbo]

AFAIK, there is no way to do this with a standard formula - you either need code, using the worksheet_change event or a User Defined Function (a coded function that can be used on a worksheet

Function CurrDate(testRng As Range)
If testRng.Value <= 5 Then
CurrDate = &quot;&quot;
Else
CurrDate = Format(Now(), &quot;dd/mm/yy hh:mm:ss&quot
End If
End Function

so you would use
=currdate(A1)
where A1 is the test cell (>5)

This will not recalc for any changes in other cells BUT will recalc if the value in the reference cell changes Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur