MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time Formulas

Posted by John Der Garabedian on September 05, 2001 9:23 AM

Am trying to input a time stamp into a cell using "Ctrl+Shift+Colon", but I need a six-digit display (hh:mm:ss)to include seconds. Excel keeps putting the time in, but the seconds always read "00".

I need to enter different times in several cells so using the NOW function doesn't work either as all of those values change whenever I enter a new value.

Any help?

Thank you.

Posted by Mark W. on September 05, 2001 9:35 AM

Enter =MOD(NOW(),1) on the formula bar, F9 and
then enter.

Posted by Mark W. on September 05, 2001 9:46 AM

Or... use this recorded VBA code snippet...

ActiveCell.FormulaR1C1 = "=MOD(NOW(),1)"
Selection.NumberFormat = "h:mm:ss"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

You can assign this to a keyboard shortcut of
your choice.