INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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?

RE: Time to the millisecond

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/

RE: Time to the millisecond

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time to the millisecond

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

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 Function 

And 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 Function 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time to the millisecond

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:

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

RE: Time to the millisecond

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

RE: Time to the millisecond

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/

RE: Time to the millisecond

Quote (mgtrp)

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

Quote (OP)

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time to the millisecond

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close