Solved

# Adding the "Centiseconds" in a TimeNow Formula?

Posted on 2006-05-20
1,094 Views
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:

12:48:55.68

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?

Thanks
0
Question by:slajoh01

LVL 80

Expert Comment

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:
=TimeNow()

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

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.

0

LVL 80

Expert Comment

slajoh01,
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:
=A2-A1

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

0

LVL 33

Expert Comment

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.

regards,
Jeroen
0

Author Comment

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...

Why?

Am I doing something wrong here?
0

LVL 31

Expert Comment

0

Author Comment

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.
0

Author Comment

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...
0

LVL 33

Expert Comment

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.

0

Author Comment

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

0

Accepted Solution

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....

PROBLEMO SOLVED...

Thanks anyway!!!
0

Author Comment

But is there anyway to make it work on version 7 though??? Any suggestions?
0

LVL 33

Expert Comment

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.

Otherwise Im not sure.

0

LVL 33

Expert Comment

No objections to me.
Jeroen
0

## Featured Post

### Suggested Solutions

Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
If your app took Google’s lash recently, here are the 5 most likely reasons.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.