Calculating Military Time and Rounding to nearest Quarter hour

[VMM]

I know this has been posted before, but I am unable to find a formula that works correctly. I would like to use a format of 0000 or 00.00.

Lets assume A1 = start time and B1=end time

If the start time is 1430 hours and the end time is 2300, how do I get results of 7.5 hours. I have tried the mround formula with .25 but it calculates 7.5 hours as 7.75 hours because it's subtracting 30 from 100 and then rounds to the nearest quarter.

I also need the formula to calculate hours that over lap into the next morning. So if the start time is 2215 and the end time is 0600, then the correct answer should be 7.75.

All of this is trying to be calculated in the same cell.
Any help would be appreciated.

Thanks.

 

[soccerkingpilot]

Are you trying to do this with VBA or just an Excel function. If you're okay with VBA, I think your best bet is to convert the minutes in your time to a fraction out of 60 and then subtract the two numbers. Here's an example:

StartTime = 1400
End Time = 1745

Make the code convert to the following:
StartTimeHour=14
StartTimeMin=0

EndTimeHour=17
EndTimeMin=.75

NewStart=14.0
NewEnd=17.75

Time=NewEnd-NewStart

Let me know if this makes sense. I can help with the code if you need too.

-Austin

 

[baitmaster]

welcome to the board

Excel time is based on a number, where 1 day = 1, and all times within a 24-hour span are a deimal value between 0 and 1. If you change your cell formatting to "number" you will see this. When you see a time value, it is the cell formatting that changes, not the underlying number, you can see this by again playing with the cell formats.

To make it simpler to work with calculations like this, you can therefore convert to decimal by simply multiplying by 24 (check the time format doesn't copy across), doing whatever calculations you want in decimal format, then dividing by 24 again. Not the neatest way of doing things, but certainly easier to understand until you are used to the nuances of Excel time / date calculations

 

[VMM]

Here is the formula I was using =mround(if(a1>b1,24-a1+b1,b1-a1),.25)... in the custom format 00.00.

If I just use a regular number format 0600 would read 600 or 6.00 depending if the decimal was used. I need the 0 to show up before the 6.

What am I doing wrong? Is it really the format I'm using?

 

[VMM]

I should point out that I have also tried this formula as well.

=if(mround(a1,.25)>mround(b1,.25),24-mround(a1,.25)+mround(b1,.25),mround(b1,.25)-mround(a1,.25)

it still calculated as previously stated in the first post

 

[VMM]

I also tried the multiply and divide by 24 (adjusted the formula to read =if(a1>b1,1-a1+b1,b1-a1)) which calculated numbers no where near 7.5 hours.

 

[soccerkingpilot]

Is this the custom format you used?

=TEXT(A1,"HHMM")

Here is the formula I was using =mround(if(a1>b1,24-a1+b1,b1-a1),.25)... in the custom format 00.00.

If I just use a regular number format 0600 would read 600 or 6.00 depending if the decimal was used. I need the 0 to show up before the 6.

What am I doing wrong? Is it really the format I'm using?

 

[VMM]

I have tried HHMM, 0000, 00.00. I just can't get it to work.

 

[VMM]

*Bangs Head Against Desk Repeatedly*

The formula cell was formatted to match the cell with the times. I'm going to go curl up in the corner and cry now... I have been working on this for over two weeks.

Thanks Everyone for helping!

 

[shg]

If you are representing time as whole numbers,

<TABLE style="WIDTH: 524pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=698><COLGROUP><COL style="WIDTH: 37pt" span=3 width=49><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 694" width=15><COL style="WIDTH: 402pt; mso-width-source: userset; mso-width-alt: 24502" width=536><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 height=16 width=49>Start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>End</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>Duration</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 width=15> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 402pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 width=536> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 align=right>1430</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right>2300</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right>830</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31>C2 and down: =DOLLARFR(DOLLARDE(B2/100, 60) - DOLLARDE(A2/100, 60) + 24*(A2>B2), 60) * 100</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 align=right>2215</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right>600</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right>745</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD></TR></TBODY></TABLE>