I need a static date/time in Excel 3

[nbay001]

Excel Office 2003

I am creating a timesheet. When a person enters their name, the cell next to it has the following formula. The formula just creates a date stamp.

=IF(L7>"",NOW(),"")

However, it's my understanding that the formula is "volatile" meaning, as soon as the next row is entered into the worksheet, it updates to the computer time.

How do I keep the time?

 

[mintjulep]

I think you need to use VBA with the Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$7" Then Worksheets("Sheet1").Range("A2").Formula = "=NOW()"
End Sub

 

[strongm]

faq707-1657

 

[AnotherHiggins]

As stated above, you'll need VBA to have the date/time automatically entered into a cell.

But just an FYI:
[tab][Ctrl]+[;] enters the current date
[tab][Ctrl]+[Shift]+[;] enters the current time
Those entries are not formulas, so they will remain static once entered.

If you want both the date and time in a single cell, then you need to add a space between the two:
[tab][Ctrl]+[;], [Space], [Ctrl]+[Shift]+[;] enters the current date and time


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlhelp]

[red]Please use with caution.[/red]

There is a way to have static date stamp using formulas and iterative function of Excel. The problem arises if there is indeed a circular reference in one of the other formulas besides the date stamp.

But, if you must, then here is the solution.

Suppose you want the static date stamp in cell B2.

First, go to Tools; Options; Calculation and put a check mark in the "Iteration" check box. Then, go to cell B2 and enter the following formula:

=IF(A2="","",IF(B2="",NOW(),B2))

As long as cell A2 is blank, there will be nothing in cell B2; but, as soon as there is an entry in A2, B2 will have static Date and time stamp.

You may need to reformat cell B2.


A man has only two choices: He can be right or he can be happy.

 

[nbay001]

Thank you all for your help.

I ended up using the following formula, which works perfect. =IF(A2="","",IF(B2="",NOW(),B2))

I have another issue. This Worksheet is run on Terminal Server, which has west coast time. It is being accessed by folks on the east coast. How do I add 3 hours to this formula to mark the time difference. I thought of seperating date from time, and just adding 3 to the hours, but that doesn't work at 9:00 p.m., when it's the next day.

Thanks,
Paula

 

[nbay001]

Never mind...i am using this: =+B2+0.125. Work fine, including switching to the next day.

 

[SkipVought]

You might find this informative...

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

First, you'd be well served to read and understand faq68-5827.

The gist is that dates are stored as whole numbers, based on the number of days since 1/1/1900. Today, Sept. 22 2009, is 40078 since it's been 40,078 days since Jan. 1 1900.

Times are stored as a decimal - a percentage of 24 hours. Noon, being half of 24 hours, is stored as 0.5. 6AM and 6PM are stored as 0.25 and 0.75, respectively.

So 3AM, or 3 hours, is stored as 0.125.

Knowing this, you can just add 0.125 to the time. If you'd like the formula to be a little more intuitive, you could use something like this:
[tab]=[YourFormula] + TIMEVALUE("3:00:00")
or
[tab]=[YourFormula] + TIME(3,,)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.