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

[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'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

[daddylonglegs]

just needs a minor tweak...

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

[azdps]

works great daddylonglegs.

[daddylonglegs]

I understand this sometimes results in incorrect results - this is due 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