Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Calculating time difference in military time

    I need to calculate the time difference using military tome to the seconds 00:00:00

    They want to enter the times not using the colons 235822 and have it convert to military time 23:58:22 which is simple enough using a custom format of 00\:00\:00

    the issue is when I try to find the deference in time or how long it took to complete a task using the formula in cell C1 =IF((B1-A1)<0,(B1-A1)*24+24,(B1-A1)*24) I even tried =B1-A1+(A1>B1)

    this formula would need to account for the change of days.


    I get errors is there a way to simplify the data entry by not using colons

    Attached is what I would like the finished product to look like only the end user would enter the data with out the additional use of Colons
    Attached Files Attached Files
    Last edited by nsmjc1; 2015-11-02 at 12:02.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by nsmjc1 View Post
    I need to calculate the time difference using military tome to the seconds 00:00:00

    They want to enter the times not using the colons 235822 and have it convert to military time 23:58:22 which is simple enough using a custom format of 00\:00\:00

    the issue is ...
    It is NOT a time value just a number formatted to look like the time.

    The only way I know to get around this is via a macro that will rip the number apart and reconstruct it as time value. However, this would require that the entered value always have six digits because any less would make it impossible for the macro to tell which value was missing a digit. Of course, having said that someone will probably prove me wrong with a very long and complicated piece of code.

    If you can live with having macros in the file and requiring 6 digits (actually the macro can enforce this) I'll be glad to gen up some code for you.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Oct 2015
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    It is NOT a time value just a number formatted to look like the time.

    The only way I know to get around this is via a macro that will rip the number apart and reconstruct it as time value. However, this would require that the entered value always have six digits because any less would make it impossible for the macro to tell which value was missing a digit. Of course, having said that someone will probably prove me wrong with a very long and complicated piece of code.

    If you can live with having macros in the file and requiring 6 digits (actually the macro can enforce this) I'll be glad to gen up some code for you.

    HTH
    That would be awesome macro is OK as long as it runs in the back ground and the user only needs to enter the numbers

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    nsmjc1,

    Well it looks like I'll make a lair out of myself!

    Here's a user defined function (udf) that will take two cell arguments and return the time difference.
    Code:
    Option Explicit
    
    
    Function CvtFmtTimeToTimeValue(rngStart As Range, rngEnd As Range) As Date
    
       Dim zFmtText As String
       Dim tmStart  As Date
       Dim tmEnd    As Date
       
       zFmtText = rngStart.Text
       
       tmStart = TimeValue(Left(zFmtText, 2) & ":" & _
                           Mid(zFmtText, 4, 2) & ":" & _
                           Right(zFmtText, 2))
                           
       
       zFmtText = rngEnd.Text
       
       tmEnd = TimeValue(Left(zFmtText, 2) & ":" & _
                           Mid(zFmtText, 4, 2) & ":" & _
                           Right(zFmtText, 2))
                           
       If (tmEnd < tmStart) Then tmEnd = tmEnd + 1
    
       CvtFmtTimeToTimeValue = tmEnd - tmStart
       
    End Function  'CvtFmtTimeToTimeValue
    You call this by using this formula: =CvtFmtTimeToTimeValue(A1,B1)
    Where: the first argument is the start time (A1 in this case)
    the second argument is the end time (B1 in this case)

    nsmjc1.JPG

    Please note:
    You must format the cells containing the entered times as per your original post!
    The cells with the calculation must be formatted with the custom format: hh:mm:ss
    The user must enter enough digits on the right of the number to clarify the time position, e.g.

    If only seconds: 3 = 3 seconds, 30 = 30 seconds
    If only minutes: 300 = 3 minutes, 3000 = 30 minutes
    etc.

    Sample File: nsmjc1.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    nsmjc1 (2015-11-02)

  6. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,172
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by nsmjc1 View Post
    ...this formula would need to account for the change of days.
    Just to clarify something... If you mean the difference between 2300 on Monday and 0700 on Tuesday, that's fairly clear because the second number is lower. But what about 1700 on Monday to 1900 on Tuesday as opposed to 1700 to 1900 on Monday?
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Graham,

    I assumed, yeah I know big mistake, that since the OP was only working with times it probably wouldn't span more than 24 hours. If that is the case the entire date/time value will have to be added. I'll wait to see what the OP has to say.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    nsmjc1 (2015-11-02)

  9. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,172
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by RetiredGeek View Post
    I assumed, yeah I know big mistake, that since the OP was only working with times it probably wouldn't span more than 24 hours.
    I ask because I've been bitten by this before now.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  10. #8
    New Lounger
    Join Date
    Oct 2015
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts
    that worked out perfectly we are only concerned with a mater of few minutes or a couple of hours it will not span days thank you for all you help

  11. #9
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi

    Or maybe a formula if you do not want to use code.

    =MOD((--TEXT(B2,"00\:00\:00"))-(--TEXT(A2,"00\:00\:00")),1) copy down and format as "hh:mm:ss or [h]:mm:ss".

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi all

    ..let's be clear here, are we talking about Zulu time?????
    ..and if not, what military unit are we referring to???

    The Navy, as well as civil aviation, uses the letter "Z" (phonetically "Zulu") to refer to the time at the prime meridian. The U.S. time zones are Eastern ["R", "Romeo]; Central ["S", "Sierra"]; Mountain ["T", "Tango"]; Pacific ["U", "Uniform"]; Alaska ["V", "Victor"], and Hawaii ["W", "Whiskey"].

    ..give me Whiskey time any day.
    (I prefer Rum, but, hey, Hawaii is such a great place)

    zeddy

  13. #11
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    566
    Thanks
    51
    Thanked 70 Times in 68 Posts
    zeddy, you are talking about timezones. Military time is a different concept, wherein you never say (am or pm), instead you use a 24-hour clock. It's just a different way of distinguishing before Noon or after Noon.

    nsmjc1 doesn't have to be concerned with timezones unless he has users updating a spreadsheet from different timezones. Which, while certainly possible, isn't the simple use-case. In fact I cannot remember designing a spreadsheet where timezones mattered.

    The simple use-case is "local time", whatever timezone that is.

  14. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    ..perhaps you are just using a '24-hour-clock', so maybe you could just use standard GMT.

    With military time: for instance, 6:00 a.m. in zone UTC−5 is written "0600R" and spoken "zero six hundred Romeo".
    The letter J ("Juliet") is used to indicate the observer's local time, as in 2100J.

    For those interested in further explanations, here are some links:
    Military Time:
    http://www.timeanddate.com/time/zones/z

    https://en.wikipedia.org/wiki/List_o...ary_time_zones

    Coordinated Universal Time
    https://en.wikipedia.org/wiki/Coordi...Universal_Time

    zeddy

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    ..in the Caribbean, there is a mix of date formats and time zones in use.
    It took me a while to properly master the issues raised in Excel when automating the updating of files from multiple zones in the region. It is not a simple issue to deal with. I don't suppose many users get exposed to operating system filedate timestamps that can be in 'the future' depending on where you are. It can be interesting to see files that appear to be 'last modifed' half-an-hour from now!

    zeddy

  16. #14
    New Lounger
    Join Date
    Oct 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The problems are
    1. the sheet needs to know the date of each time references. To do this, you need a date column for each time which, of course, can be hidden.
    2. If you intend to have a total of hours that is greater than 23:59:59, or one day, then this format is not going to work, because that is the limit of the function and after that, it will produce an error.

    So you have to have
    a column for the first date – with the cell formatted as a date (right click choose ‘format cell’)
    a column for the first time – with the cell formatted as hh:mm:ss (right click choose ‘format cell’)
    a column for the second date – with the cell formatted as a date (right click choose ‘format cell’)
    a column for the second time – with the cell formatted as as hh:mm:ss (right click choose ‘format cell’)
    So you have

    ......A..............B....................C....... ........D.....................E
    Start date ..| Start time |…. End Date…. | End time | = (C2-A2) + (D2-B2)
    01/11/2015…. .23:00:00..…..02/11/2015.… 02:00:00….= 3:00:00
    Last edited by PaulQ; 2015-11-05 at 06:48.

  17. #15
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,172
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Zeddy,

    The OP is more correctly referring to a 24hr clock (which is used in a number of European countries) than military time, even though 24hr is often referred to as military time in the US.

    It's unfortunate that military and 24hr time notations have been mixed because they are different (0900 vs 9:00) and military times are frequently Zulu/GMT/UTC times rather than local. For example, 7pm would be 19:00 (spoken as 19 o'clock) in 24hr time or 1900hr (spoken as nineteen-hundred hours) in military time.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •