Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Calculate Military Times Without Colon

  1. #1
    Join Date
    19th November 2006
    Posts
    18

    Calculate Military Times Without Colon

    I just want to shear a formula which will help if you to calculate time stored as text for example

    from | to |worked
    1800 | 2400| 6

    I searched the forum earlier and could not find anything simular
    please note that the cell format for column A and B is custom 0000 in case values like 0100 have to be entered

    the formula placed in c1 is:
    =(INT(a1/100)+((a1-INT(a1/100)*100)/60))-(INT(B1/100)+((B1-INT(B1/100)*100)/60))

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Time As Text Calculations

    =VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2))+(VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))>VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2)))-VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))

    Where B1 is the "To" time. Format result as required.

    See Excel Date and Times
    Last edited by Dave Hawley; December 14th, 2006 at 15:39.

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,364

    Re: Time As Text Calculations

    Hi,

    Try,

    =--TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)

    Format the cell as hh:mm

    HTH

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  5. #5
    Join Date
    19th November 2006
    Posts
    18

    Re: Time As Text Calculations

    KK is there any reson for the double -- and the 0 in your formula.

    Quote Originally Posted by Krishnakumar
    Hi,
    =--TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)
    HTH

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,364

    Re: Time As Text Calculations

    both of them converts text value to number value

    =TEXT(B1,"00\:00")+0-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)

    or

    =--TEXT(B1,"00\:00")-(--TEXT(A1,"00\:00"))+--(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00"))
    Last edited by Krishnakumar; December 14th, 2006 at 20:09.

  7. #7
    Join Date
    3rd November 2012
    Posts
    1

    Re: Calculate Military Times Without Colon

    This seems similar ..... found it at timecardworkbook.com - if the 4 digit military time is in cell A1 and this formula is in B1:


    =IF(A1="",0,+VALUE(+CONCATENATE(+(A1-+RIGHT(A1,2))/100,":",+RIGHT(A1,2))))this worked for me!Thanks for the forum ....

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    2nd July 2012
    Posts
    36

    Re: Calculate Military Times Without Colon

    Hi
    Another version which might be of helpl! =(--(LEFT(B1,2)&":"&RIGHT(B1,2)))-(--(LEFT(A1,2)&":"&RIGHT(A1,2)))

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Calculate Difference Between Times In 1 Cell
    By leonardwsimpson in forum EXCEL HELP
    Replies: 2
    Last Post: January 10th, 2007, 03:39
  2. Calculate Start/End Times
    By kohoutek in forum EXCEL HELP
    Replies: 11
    Last Post: December 20th, 2006, 15:41
  3. Calculate Times Past Midnight
    By Malathay in forum EXCEL HELP
    Replies: 2
    Last Post: December 8th, 2006, 14:52
  4. Converting military times to numbers
    By Wire323 in forum EXCEL HELP
    Replies: 7
    Last Post: April 6th, 2006, 18:24
  5. Enter military time without colon
    By XL-Dennis in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: March 21st, 2003, 22:51

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
  •  
porno