Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
Converting Standard Time into Military Time | Excel Discussion (Misc queries) | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
days caculation | Excel Worksheet Functions |