Sunday, February 27, 2011

yogi_InsertHH:MM:SSforCurrentTimeAtTimesAndHold


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com


glrcbs said ...

I need a shortcut to insert the current time of day in hours, minutes, and seconds, into a spreadsheet cell, preferably in HH:MM:SS format and need to be able to do that multiple times in a spreadsheet
I've tried  =NOW()

which correctly inserts the current time (and date) but whenever I add a new time, all those cells are updated to the newest time


I've tried ctl+:  


but it puts zeroes where the seconds should be


And I've tried     

=mid(index(ImportHtml("http://www.time.gov/timezone.cgi?Eastern/d/-5";"table";3);2;1);2;9)

which works the first time I use it in a spreadsheet. Every time I use it after that, it enters the same time as the first one.

Any suggestion?

-----------------------------------------------------------------------------------

As you have already noticed Ctrl+: or Ctrl-Shift-; as Gill suggested embed the current time in hh:mm:00 (with seconds as 0)

I have the following workaround for you using Data Validation from a List ... 
the List consists of one cell with the following formula ...
=mod(now(),1)

Check it out and see if this would work for you!