SOLVED

Auto update of time function

%3CLINGO-SUB%20id%3D%22lingo-sub-2019034%22%20slang%3D%22en-US%22%3EAuto%20update%20of%20time%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2019034%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20preparing%20a%20project%20log%20using%20excel%20and%20the%20now%20function.%20My%20problem%20is%20auto-updating%20of%20the%20worksheet%20results%20in%20time%20difference%20%3D0%3F%20How%20to%20do%20it%3F%20I%20usually%20want%20auto-update.%20I%20am%20not%20sure%20how%20to%20change%20this%20option%3F%20Can%20you%20help%3F%20Thank%20you.%3CBR%20%2F%3E%3CBR%20%2F%3ESincerely%2C%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3BMM%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2019034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2019071%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20update%20of%20time%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2019071%22%20slang%3D%22en-US%22%3EThe%20NOW%20function%20is%20volatile%20and%20re-calculates%20whenever%20Excel%20re-calculates.%20You%20could%20use%20the%20keyboard%20shortcut%20Ctrl%2BShift%2B%3A%20to%20enter%20the%20time.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2019197%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20update%20of%20time%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2019197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20the%20shortcut%20you%20described%20the%20insert%20menu%20opens%20and%20the%20two%20cells%20to%20the%20right%20have%20the%20current%20time%20inserted%3F%20This%20is%20unreal!%26nbsp%3B%20MM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2019203%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20update%20of%20time%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2019203%22%20slang%3D%22en-US%22%3Econtrol%20shift%20semicolon%20works%20but%20it%20also%20works%20for%20two%20other%20time%20cells%20in%20the%20same%20row%20(to%20the%20right).%20I%20need%20to%20stop%20that.%20How%3F%20MM%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2019204%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20update%20of%20time%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2019204%22%20slang%3D%22en-US%22%3EDon't%20actually%20hit%20the%20%22%2B%22%20key.%20The%20%22%2B%22%20key%20just%20means%20to%20hit%2Fhold%20the%20other%20keys%20at%20the%20same%20time.%3CBR%20%2F%3E%3CBR%20%2F%3EHold%20down%20the%20Ctrl%20and%20Shift%20keys%2C%20then%20hit%20the%20%22%3A%22%20key.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

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

@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

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
Best Response confirmed by mmarion (Occasional Contributor)
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.
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
To clarify:
Date Name Project Time1 Time2 Time
20/12/28 XXX A. XXXXXX admin 17:31:00 17:32:00 17:32:00
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

@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

You mean 13:12:10 does not refer to 13:00(1pm) , 12 min, and 10 seconds?
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.
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.

@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

@mmarion 

 

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

 

JMB17_0-1609208731181.png

 

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.

JMB17_2-1609208890194.png

 

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.

 

 

 

Also, when you save the workbook, save it as a macro-enabled workbook (*.xlsm).
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

@mmarion 

 

This should do it:

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