How do I create an automatic date entry?

[Guest]

I have entered the following formula into my spreadsheet:
=IF(F8="Complete",TODAY(),"")
The formula enters today's date when I enter the word "complete" into F8.
However, tomorrow, the formula will update to tomorrow's date. Ho can I keep
the date from changing every day after the initial entry?
Thanks,

 

[Dave Peterson]

You can use a shortcut key of ctrl-semicolon (ctrl- to enter the static time.

Or you can use a macro to plop in the date/time.

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

 

[Gord Dibben]

Event code.......

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Address = "$F$8" Then
If Target.Value = "Complete" Then
Excel.Range("G8").Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste into that module.


Gord Dibben MS Excel MVP

 

[Guest]

This question is so similar to the one that I have....

how could I accomplish this..=IF(F8="Complete",TODAY(),"")

BUT with any text (not a specific word like 'Complete') and in the situation
where any change in a row (a few columns with text and a date column) will
automatically update the date.

Thanks
June

 

[Roger Govier]

Hi June

Maybe
=IF(ISTEXT(F8),TODAY(),"")

--
Regards

Roger Govier

This question is so similar to the one that I have....

how could I accomplish this..=IF(F8="Complete",TODAY(),"")

BUT with any text (not a specific word like 'Complete') and in the
situation
where any change in a row (a few columns with text and a date column)
will
automatically update the date.

Thanks
June

 

[Guest]

With the formula below how do i change it so it shows date and time?

 

[Fred Smith]

Change Today() to Now(), and custom format the date/time.

 

[Guest]

what is the custom format for the date/time format
Wednesday, 17 October 2007, 15:25

 

[David Biddulph]

Try dddd, dd mmmm yyyy, hh:mm
If you look at the custom formats in the default list, you can then vary the
parameters to suit your requirements.

 

[Peo Sjoblom]

The format is

dddd, dd mmmm yyyy, hh:mm

but you can't make it static by formatting


http://www.mcgimpsey.com/excel/timestamp.html




--

Regards,

Peo Sjoblom

 

[Fred Smith]

Tell us what you tried, and why it didn't do what you want. Then we're better
able to help you.