Military time caculation help needed
 

*_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 :)


--
azdps
------------------------------------------------------------------------
azdps's Profile: http://www.excelforum.com/member.php...o&userid=37199
View this thread: http://www.excelforum.com/showthread...hreadid=569113

Military time caculation help needed
 

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

--
oldchipp
-----------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...fo&userid=1990
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

Try this

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

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

Sorry, should be ROUNDUP, not ROUND

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

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

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

--
azdp
-----------------------------------------------------------------------
azdps's Profile: http://www.excelforum.com/member.php...fo&userid=3719
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

just needs a minor tweak...

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

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

impressive. thanks daddylonglegs it works grea

--
azdp
-----------------------------------------------------------------------
azdps's Profile: http://www.excelforum.com/member.php...fo&userid=3719
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

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

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

Works well and so far no issues found. Thanks again

--
azdp
-----------------------------------------------------------------------
azdps's Profile: http://www.excelforum.com/member.php...fo&userid=3719
View this thread: http://www.excelforum.com/showthread.php?threadid=56911

Military time caculation help needed
 

azdps Wrote:
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)*1LEFT(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 :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=569113