+ Reply to Thread
Results 1 to 7 of 7

Military Time Elapsed Formula (HELP ME)

  1. #1
    Registered User
    Join Date
    12-05-2014
    Location
    Florida
    MS-Off Ver
    Office 365
    Posts
    1

    Military Time Elapsed Formula (HELP ME)

    I'm looking for a formula to calculate time elapsed when using military time.

    Row A B C
    1 1800 1854 54
    2 1854 1918 24
    3 1918 1948 30
    4 1948 2030 42
    5 2030 2110 40
    6 2110 2258 108
    7 2258 2323 25
    8 2323 2356 33
    9 2356 0004 8

    I'm about 1,900 rows (that's not an exaggeration) into this document and up until this point I've just been calculating everything mentally as I go but my head might very well explode so a formula would be greatly appreciated. Obviously, I know how to execute simple subtraction formulas but there are instances (i.e. row 4 and 9) where I'm a little out of my depth. I guess I need an IF formula for when B<A and for when B goes into the next hour? Subtract 40 in that case? I don't know. Help me.

    Also, like I said, I'm WELL into this document so changing the format from 1800 to 18:00 is just not an option. I know, I should have done that from the beginning and saved myself the misery.

    Thanks in advance!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    Re: Military Time Elapsed Formula (HELP ME)

    In D1 copied down

    =MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)

    Format the cells as Custom [mm]
    See attached.
    Attached Files Attached Files
    Last edited by ChemistB; 12-05-2014 at 03:04 PM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Military Time Elapsed Formula (HELP ME)

    If you want this in time format format columns C as Custom "h:mm" and use formula in C1

    =REPLACE(B1,3,,":")-REPLACE(A1,3,,":")

    Row\Col
    A
    B
    C
    1
    1800
    1854
    0:54
    2
    1854
    1918
    0:24
    3
    1918
    1948
    0:30
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    10-29-2014
    Location
    Massachusetts
    MS-Off Ver
    2013
    Posts
    26

    Re: Military Time Elapsed Formula (HELP ME)

    I would go with AlKey's solution.

    Optionally: Since time is stored as decimal value with 24 hours being 1.00000, if you need the minutes to be stored as whole numbers, you could multiply the output with 24 and 60:
    =( REPLACE(B1,3,,":")-REPLACE(A1,3,,":") ) * 24 * 60

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    Re: Military Time Elapsed Formula (HELP ME)

    Alkey's solution will not fix the issue when the time goes into the next day. You would need to nest it in the MOD function

    =MOD(REPLACE(B1,3,,":")-REPLACE(A1,3,,":"),1)

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Military Time Elapsed Formula (HELP ME)

    Thank you ChemistB. Looks like I missed that part. I did look into the adding MOD but results are come out somewhat different

    =MOD(REPLACE(B1,3,,":")-REPLACE(A1,3,,":"),1) in column F and modified formula in E

    =IF(B1<A1,("24:00"-REPLACE(A1,3,,":"))+SUBSTITUTE(B1/100,".",":"),REPLACE(B1,3,,":")-REPLACE(A1,3,,":"))

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    1800
    1854
    54
    54
    0:54
    0:54
    2
    1854
    1918
    24
    24
    0:24
    0:24
    3
    1918
    1948
    30
    30
    0:30
    0:30
    4
    1948
    2030
    42
    42
    0:42
    0:42
    5
    2030
    2110
    40
    40
    0:40
    0:40
    6
    2110
    2258
    108
    108
    1:48
    1:48
    7
    2258
    2323
    25
    25
    0:25
    0:25
    8
    2323
    2356
    33
    33
    0:33
    0:33
    9
    2356
    4
    8
    08
    0:08
    4:04
    10
    2356
    25
    29
    0:29
    1:04
    11
    2356
    59
    63
    1:03
    11:04

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Military Time Elapsed Formula (HELP ME)

    More ways:

    Row\Col
    A
    B
    C
    D
    E
    1
    Start
    Stop
    Dec Min
    Time
    2
    1800
    1854
    54
    0:54
    C2: =MOD(60*(DOLLARDE(B2/100,60)-DOLLARDE(A2/100,60)),1440)
    3
    1854
    1918
    24
    0:24
    D2: =MOD((DOLLARDE(B2/100,60)-DOLLARDE(A2/100,60))/24, 1)
    4
    1918
    1948
    30
    0:30
    5
    1948
    2030
    42
    0:42
    6
    2030
    2110
    40
    0:40
    7
    2110
    2258
    108
    1:48
    8
    2258
    2323
    25
    0:25
    9
    2323
    2356
    33
    0:33
    10
    2356
    4
    8
    0:08
    11
    2356
    25
    29
    0:29
    12
    2356
    59
    63
    1:03
    Last edited by shg; 12-05-2014 at 04:07 PM.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dividing af formula for elapsed time with a fixed(static) time
    By cbend001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-03-2014, 09:37 PM
  2. Simple way to convert military time to standard where military has no colon
    By salvator in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2011, 10:27 AM
  3. Replies: 2
    Last Post: 04-27-2011, 08:21 AM
  4. Replies: 7
    Last Post: 05-18-2009, 02:00 PM
  5. time elapsed formula
    By sue in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2006, 10:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1