Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Calculate Military Times Without Colon

1. I agreed to these rules
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. ## 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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## 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. ## Re: Time As Text Calculations

5. I agreed to these rules
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.

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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## 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. I agreed to these rules
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. ## 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

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

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