• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1242
  • Last Modified:

Adding the "Centiseconds" in a TimeNow Formula?

1. How can I ADD the CENTISECONDS which is this..1/100 to a "time now" formula? Because I this is what I had saw in a TN3270 emulator:


I want to do the same thing in Excel or in a spreadsheet to add the 1/100 of a second in my spreadsheet project...

How this be accomplished as a Time Now formula? Please look at my example above were the decimal (.68) is..

How can I do this in a spreadsheet?

  • 6
  • 4
  • 2
  • +1
1 Solution
Hi slajoh01,
You need to use a Custom format like h:mm:ss.00

You can then use the NOW() function to get this degree of precision, or a UDF with a worksheet formula like:

Function TimeNow() As Double
TimeNow = Timer / 86400
End Function

To install a function in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

Optional: to add descriptive text (appears at bottom of Function Wizard):
5) ALT + F8 to open the macro window
6) Type in the name of your function in the "Macro name" field at the top
7) Press the "Options" button
8) Enter some descriptive text telling what the function does in the "Description" field
9) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Hoping to be helpful,

To get the difference between an ending time and starting time (assuming they don't go past midnight), just subtracting the starting from the ending:

As mentioned in the previous comment, you'll need to use a Custom cell format to get it to display the hundredths of a second. You create this Custom format by selecting the cells to receive it, then opening the Format...Cells menu item. Go to the Number tab, click Custom on the left and then enter h:mm:ss.00 in the field on the right.

If you are entering data, please format the cells that receive those times with the Custom format before entering the data. Also, be sure to enter a placeholder for the hours, even if all you want are minutes and seconds. For example: 0:13:32.93

Dates and Times
Excel stores dates and times as date/time serial numbers. These have an integer part representing the day and a fractional part representing the hours. The start of dates and times (in Windows Excel) is January 0, 1900. Each day after then adds one to the total. So 12:00 AM on January 1, 1900 is 1.0, and 12:00 PM on January 1, 1900 is 1.5. When a correctly formatted date and/or time Excel is entered Excel automatically converts the value entered into a date value.

If you enter 3 PM without a date then Excel assumes the date part is 0 so the value stored is 0.625 = (12 + 3)/24.

Date and Time Math
To find the number of days between two dates, just subtract one from the other: February 10, 2005 - January 25, 2005 = 38,377 - 38,393 or 16 days. Dates with times can be manipulated the same way: The difference in days between 6:00 AM on February 2, 2005 and 12:00 PM on January 31, 2005 is 38,385.25 - 38,383.50 or 1.75 days. 1.75 days is really 1 day and 18 hours where 18 is ¾ or .75 of one day.

Use caution when performing math with dates containing just times. For example, subtracting a start time of 9 PM from a stop time of 3 AM will not produce 0.25 or 6 hours but rather will produce -0.75 or -18 hours. This is due to the fact that, without a date part, the times are just fractions of a day and Excel does not know which day to which they belong:
3 AM - 9 PM = 0.1250 - 0.8750 = -0.75 = -18 hours         (which Excel won't display, because it is before time began--a negative date/time serial number)

This is easily fixed by adding 1 to the 3 AM date value because it is really one day in the future:
3 AM + 1 - 9 PM = 0.1250 + 1 - 0.8750 = 1.1250 - 0.8750 = 0.25 = 6 hours

Often, you don't know if the stop time will occur on the same day or the next day. If the difference between the two times will never be more than 24 hours then use this formula:
HoursWorked = IF(StopTime <= StartTime, StopTime + 1 - StartTime, StopTime - StartTime)

Calculating Hours, Minutes, and Seconds
Given any date which is entered as a date or the result of date math, the number of hours, minutes, or seconds represented by that date can be easily determined. For example, given the result of the math above, 1.75 or 1 day and 18 hours, the total number of hours represented by that date value is calculated by multiplying by 24:
= 1.75 * 24 = 42

The number of minutes is calculated by multiplying by 24 * 60:
= 1.75 * 24 * 60 = 2,520

The number of seconds is calculated by multiplying by 24 * 60 * 60:
= 1.75 * 24 * 60 * 60 = 151,200

Jeroen RosinkCommented:
Here a small contribution based on cell formating.
If you use the Cell custom formating like:  hh:mm:ss.00 then it will aslo show the centiseconds.

I place in cell A1 the formula: =NOW() and formated the cell this way.
Pressing the F9 (caluclation) key changed the centiseconds value.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

slajoh01Author Commented:
I went to the FORMAT CEALLS secion and then I went to time section and then at the bottom blank area I entered the date and time for timenow like this...And everytime I enter something and press enter, the .00 WILL NOT chenge at all....How come?

I did it like this here below:

m/dd/yy HH:MM:SS.00

Like said, I entered only one character to rest it out and pressed on ENTER, NOTHING happened with the last .00 (1/100 sec.) digits...


Am I doing something wrong here?
slajoh01Author Commented:
Guys...I tried...like this...

m/dd/yy hh:mm:ss.00 in that blank box after you slect Format Cells...and it DOES NOT WORK....The .00 does not move or change at all...Only the last second.

Please, can you just post an example or an easier example on how to do this? I already did the timeNow formula...And also I entered in that box theformat like I posted above, but it will not change at all...

What can I do. Please explain.
slajoh01Author Commented:
The digit that will change is the seconds...Thats it...NOT the .00..I tried everything....like you told me. And this is just ONLY in cell A-1, which I ONLY want to use as the date/time.....area. NOTHING else....

Like I said, I entered like this under USER DEFINED...

m/dd/yy hh:mm:ss.00

And the .00 WILL NOT change...
Jeroen RosinkCommented:
If im correct the .00 will only change when there is some kind of formula behind it. like:
=Now()  as formula in the cell and format the cell like: m/dd/yy hh:mm:ss.00
now press F9 button which makes a new calculation, and updating the time value.

if you have a date/time entered in the cell then the centiseconds won't change as the cell value is also not changing.

slajoh01Author Commented:
In StarOffice 8 it works....But in StarOffice 7...IT DOES NOT....

slajoh01Author Commented:
IT DOES NOT WORK IN STAR-OFFICE 7... ONLY on VERSION 8....Thats the problem...

Problem solved...

It worked under Windows 2000 because I downloaded StarOffice 8 there....But in Solaris 10, StarOffice comes preloaded with version 7 which does NOT support the 1/100 seconds thing....


Thanks anyway!!!
slajoh01Author Commented:
But is there anyway to make it work on version 7 though??? Any suggestions?
Jeroen RosinkCommented:
I'm just getting a bit lost, I thought it has to do with excel itself and not a Staroffice variant. Still good to hear it also works in version 8.

Ain't there a standard upload utility in staroffice to obtain the latest version?
Otherwise Im not sure.

Jeroen RosinkCommented:
No objections to me.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now