## Time to the millisecond

## Time to the millisecond

(OP)

I may have asked this before, but if I did I never got a workable answer so I'll try again...

I need to work with times to the nearest millisecond (and can foresee microsecond times at some point in the future) as I establish times and reference times for events on the electric grid. I can apply a cell format of HH.mm.ss.000 and have the full time displayed. That part works fine. What doesn't seem to work is the ability to edit that number. If I try to edit, either in the cell or in the entry area above the spreadsheet, excel (2010) rounds the time to the nearest HH.mm.ss. I always have to retype the milliseconds portion. Sometimes that's easy enough, add the dot and three digits. Other times it's a true PITA; I'm presently working on events that occurred in the last half second of 9:59:59 - if I want to edit 9:59:59.587 and change it to 9:59:59.592 I have to reenter the whole thing because hitting edit causes 9:59:59.587 to become simply 10:00:00.

Is there some way, some setting, to cause excel understand that time has far finer gradations than seconds? I deal in fractions of electrical power system cycles and there's 60 of them (ideally, where I am) every second. One cycle is 16.667 milliseconds. One degree is 46 microseconds. If I deal with traveling waves, 1 microsecond is 1000 feet; the nearest second is many times around the earth.

Is there some way to force excel to work in time units considerably smaller than a second? Or, am I simply stuck with the brain dead units of time that I seem to be stuck with?

I need to work with times to the nearest millisecond (and can foresee microsecond times at some point in the future) as I establish times and reference times for events on the electric grid. I can apply a cell format of HH.mm.ss.000 and have the full time displayed. That part works fine. What doesn't seem to work is the ability to edit that number. If I try to edit, either in the cell or in the entry area above the spreadsheet, excel (2010) rounds the time to the nearest HH.mm.ss. I always have to retype the milliseconds portion. Sometimes that's easy enough, add the dot and three digits. Other times it's a true PITA; I'm presently working on events that occurred in the last half second of 9:59:59 - if I want to edit 9:59:59.587 and change it to 9:59:59.592 I have to reenter the whole thing because hitting edit causes 9:59:59.587 to become simply 10:00:00.

Is there some way, some setting, to cause excel understand that time has far finer gradations than seconds? I deal in fractions of electrical power system cycles and there's 60 of them (ideally, where I am) every second. One cycle is 16.667 milliseconds. One degree is 46 microseconds. If I deal with traveling waves, 1 microsecond is 1000 feet; the nearest second is many times around the earth.

Is there some way to force excel to work in time units considerably smaller than a second? Or, am I simply stuck with the brain dead units of time that I seem to be stuck with?

## RE: Time to the millisecond

One possibility is to use =Time(9,59,00)+(59.592/(24*3600)) and format it to show the microseconds.

You could also write your own msTime function in VBA, so you could enter the seconds in the function, and have it return the date/time number, calculated as above. That way the edit line will display the function, so you won't lose the milliseconds, and you could go to greater precision if you need to. Let me know if you would like some code for that.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Time to the millisecond

Excels Date/Time UNITS are DAYS.

The Date/Time serial value for 9:59:59 is 0.416655092592593 DAYS.

I'd suggest converting your Time values (DAYS) to MS (or HR to MS + MM to MS + SS to MS) and then adding the residual MS. Then

perform all your math in MSand then calculate HR, MM, SS, MS of your final result for a display value in a TEXT formatted cell or preceded by an APOSTROPHY, as '9:59:59.587Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Time to the millisecond

## CODE

And a function to convert a Date/Time value to MS

## CODE

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Time to the millisecond

I don't actually think there is a need to convert the date value to text. If a number is returned as a double from a function, Excel is quite happy to display it in time format, with milliseconds, so my UDF combines seconds, minutes, hours and days to a date value, and returns that number as a double:

## CODE -->

See the attached file for working code and a few examples. Note that you can omit any or all values except the seconds, and enter the values as cell references or directly as numbers.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Time to the millisecond

## RE: Time to the millisecond

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Time to the millisecond

The fact that you can still do arithmetic on these TEXT values (

_{since Excel does the same text to Date/Time CONVERSIONS as when you enter Date/Time appearing characters http://www.tek-tips.com/faqs.cfm?fid=5827})is a revelation.I was not aware of that.

So it makes the OP's lament... void

Thank you mgtrp and have a little purple star.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Time to the millisecond