I want to fill a cell with the current time, so it changes every time I open the file.
I currently use the
NOW() function for that, but it returns both the date and the time.
I want to see only the time. Is there a way to do that?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
You could try the following:
TIME(VALUE(LEFT(TEXT(NOW(), "HH:MM:SS"), 2)), VALUE(LEFT(RIGHT(TEXT(NOW(), "HH:MM:SS"), 5), 2)), VALUE(RIGHT(TEXT(NOW(), "SS:MM:SS"), 2)))
This will effectively break up the hours, minutes and seconds returned from the format function and format them as a time. I haven't tried it in the afternoon, so I'm not sure if you get problems with am/pm.
In time-related calculations you could use the following formula:
It returns the same value as @ChrisNeilsen's formula, but using less steps and getting the current date only once. The downside of this formula is the use of division.
In Google Sheets you could use the following hack instead:
TIMEVALUE() function expects a string, but it works anyway)
here is a link showing how to do this CLOCK WITH AUTOMATIC REFRESH
in line With Sheet1.Range("A1") replace Sheet1 with actual sheet name where you want this clock and A1 with cell where you want your clock