Time to the millisecond 4

[davidbeach]

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?

 

[IDS]

My first thought was to use the time function, but it seems that Excel Time is always rounded to the nearest second, so that doesn't work.

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/

 

[SkipVought]

Hi,

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's a function to display HH:MM:SS.mmm given MS. Use this function on a sheet like any spreadsheet function...

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 Function

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

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 Function

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

If Skip is posting code, I'd better do likewise.

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:

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 Function

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/

 

[mgtrp]

I don't have any nice VBA script, but...if you enter your times with an apostrophe at the start, forcing Excel to treat them as text, you can still do some basic math on them as if they were a time (add, subtract, etc), and you will be able to edit the milliseconds without any issues.

 

[IDS]

mgtrp - Good point, that's probably the easiest way to handle it for the purposes in this example. All the text times are rounded to the nearest millisecond, even if you enter more significant figures, but at the millisecond level it seems to do subtraction and addition correctly.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

and you will be able to edit the milliseconds without any issues

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

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

void

Thank you mgtrp and have a little purple star.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[davidbeach]

Thanks mgtrp; that's probably the easiest solution. I've been aware that excel would do math on numbers entered as text and had not though of that approach.