Auto update of time function

[mmarion]

I am preparing a project log using excel and the now function. My problem is auto-updating of the worksheet results in time difference =0? How to do it? I usually want auto-update. I am not sure how to change this option? Can you help? Thank you.

Sincerely,
 MM

[#2]

The NOW function is volatile and re-calculates whenever Excel re-calculates. You could use the keyboard shortcut Ctrl+Shift+: to enter the time.

[#3]

@JMB17 

When I use the shortcut you described the insert menu opens and the two cells to the right have the current time inserted? This is unreal!  MM

[#4]

control shift semicolon works but it also works for two other time cells in the same row (to the right). I need to stop that. How? MM

[#5 · Accepted solution]

Don't actually hit the "+" key. The "+" key just means to hit/hold the other keys at the same time.

Hold down the Ctrl and Shift keys, then hit the ":" key.

[#6]

Ok. start time and end time can be entered using control shift colon. How should I adjust the subtract time cell? I am using =TEXT(E2-D3,"HH:MM:SS"). It is giving the wrong answer (end time)? MM

[#7]

To clarify:
Date Name Project Time1 Time2 Time
20/12/28 XXX A. XXXXXX admin 17:31:00 17:32:00 17:32:00

[#8]

I suspect the formula should be E2-D2 and not E2-D3?

Also, I just noticed that shortcut does not appear to include the seconds. If that is an issue, then you may consider creating a button and attaching a macro to add the time to the current cell.

Sub GetTime()
activecell.Value = time
End Sub

[#9]

@JMB17 

All cells are working properly now. Thank you for your help.

I wonder if this could be done without shortcut keys but rather with excel commands of some kind? That is what I was attempting to do.

MM

[#10]

You mean 13:12:10 does not refer to 13:00(1pm) , 12 min, and 10 seconds?

[#11]

You could use the now function, but you would need to hardcode the result to keep it from re-calculating. And, it appears copy/paste special triggers the now function to re-calculate, so excel would have to be in manual calculation mode when you did it.

[#12]

On my machine, the seconds are truncated. I couldn't tell you what the difference is, but it appears to be including seconds for you.

[#13]

@JMB17 

You are helping me learn what I need to know. How do I make a button witch calls a macro? I see your code....  MM

[#14]

@mmarion 

Open the vba editor (Alt F11). Select your file in the project explorer (Ctrl R if the project explorer window is not open.

Then, click Insert, Module. Double click the newly added module and it should open a code window. Then, copy/paste the macro into the code module.

Close the vba editor. From the Excel ribbon, click developer tab (File/Options/Customize Ribbon and check developer in the main tabs listbox if you don't see the developer tab). Click Insert, Button (form control). Draw the button on your worksheet and a window should open to select a macro to assign to the button - pick the gettime macro.

[#15]

Also, when you save the workbook, save it as a macro-enabled workbook (*.xlsm).

[#16]

This works very nicely. How can I get 24 hour time in this macro? I use something like 17:31:01. I don't like using AM and PM. Thank you for responding to my questions. I am making progress. MM

[#17]

@mmarion 

This should do it:

Sub GetTime()
     ActiveCell.Value = Time
     ActiveCell.NumberFormat = "hh:mm:ss"
End Sub