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 MS and 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.587
Skip,
for a NUance!
RE: Time to the millisecond
CODE
Function DisplayTimeMS(ms As Long) As String 'SkipVought 5/19/2017 'returns a string HH:MM:SS.mmm given MS Dim iHR As Long, iMM As Long, iSS As Long Const MSPHR = 3600000 Const MSPMM = 60000 Const MSPSS = 1000 'MS to hours iHR = Int(ms / MSPHR) 'MS to mins ms = ms - iHR * MSPHR iMM = Int(ms / MSPMM) 'MS to secs ms = ms - iMM * MSPMM iSS = Int(ms / MSPSS) 'MS ms = ms - iSS * MSPSS DisplayTimeMS = _ Format(iHR, "00") & ":" & _ Format(iMM, "00") & ":" & _ Format(iSS, "00") & "." & _ Format(ms, "000") End FunctionAnd a function to convert a Date/Time value to MS
CODE
Function TimeToMS(dTM As Date) As Long 'SkipVought 5/19/2017 'returns MS given a Date/Time value (hh:mm:ss) Const MSPHR = 3600000 TimeToMS = dTM * 24 * MSPHR End FunctionSkip,
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 -->
Function msTime(Secs As Double, Optional Mins As Double, Optional Hours As Double, Optional days As Double) As Double Const SecsinDay As Long = 86400, MinsinDay As Long = 1440, HoursinDay As Long = 24 msTime = days + Hours / HoursinDay + Mins / MinsinDay + Secs / SecsinDay End FunctionSee 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