Military time caculation help needed

[azdps]

*_Time_Table_*
1-6 *.1*
7-12 *.2*
13-18 *.3*
19-24 *.4*
25-30 *.5*
31-36 *.6*
37-42 *.7*
43-48 *.8*
49-54 *.9*
55-60 *1.0*

Im working with military time. On my spreadsheet I need the times shown
as 0930 and not 09:30. A1 holds the begining time and B1 holds the
ending time. The above listed table shows how my time needs to be
rounded. For example if I worked from 0930 to 0957 the time worked
would be 0.5.

A1 = *0930*
B1 = *0957*
C1 = *0.5*

Another example would be if I worked from 2200 to 2312 the time worked
would be 1.2.

A1 = *2200*
B1 = *2312*
C1 = *1.2*

All input welcome

 

[oldchippy]

Hi azdps,

If the input times are formatted as text so that the leading zero's ar
shown in the spreadsheet, this formula seems to work

=LEFT(B1,2)*1-LEFT(A1,2)*1+ROUNDUP((RIGHT(B1,2)*1-RIGHT(A1,2)*1)/6,0.5)/10

Give it a try and let me know

oldchippy :cool

 

[daddylonglegs]

Try this

=ROUND((TEXT(B1,"00\:00")-TEXT(A1,"00\:00"))*24,1

 

[daddylonglegs]

Sorry, should be ROUNDUP, not ROUND

=ROUNDUP((TEXT(B1,"00\:00")-TEXT(A1,"00\:00"))*24,1

 

[azdps]

Thank you oldchippy and daddylonglegs both versions worked like a charm
I have one issue I need resolved now that I forgot to orginally mention
If my time falls on a new day the calculations that you provided don'
work correctly. The calculation would provide a negative number sinc
B1 appears to be a lower number than A1, but actually its just the nex
day. Example:

A1 = *2330*
B1 = *0140*
C1 = *2.2

 

[daddylonglegs]

just needs a minor tweak...

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

 

[azdps]

impressive. thanks daddylonglegs it works grea

 

[daddylonglegs]

I understand this sometimes results in incorrect results - this is du
to rounding errors. This should fix it

=ROUNDUP(MOD(ROUND((TEXT(B1,"00\:00")-TEXT(A1,"00\:00"))*1440,0)/1440,1)*24,1

 

[azdps]

Works well and so far no issues found. Thanks again

 

[oldchippy]

Thank you oldchippy and daddylonglegs both versions worked like a charm.
I have one issue I need resolved now that I forgot to orginally mention.
If my time falls on a new day the calculations that you provided don't
work correctly. The calculation would provide a negative number since
B1 appears to be a lower number than A1, but actually its just the next
day. Example:

A1 = *2330*
B1 = *0140*
C1 = *2.2*

Hi azdps,

Sorry for delay in getting back to you, but if you still want an
answer, this takes into account for times going into the next twenty
four hours

=IF(LEFT(A1,2)*1>LEFT(B1,2)*1,ROUNDUP((2360-LEFT(A1,4)*1+RIGHT(B1,2)*1)/6,0.05)/10+LEFT(B1,2)*1,LEFT(B1,2)*1-LEFT(A1,2)*1+ROUNDUP((RIGHT(B1,2)*1-RIGHT(A1,2)*1)/6,0.5)/10)

oldchippy