Calculating Hours with fractions for half hours

[JAATL2014]

I have a formula to calculate the number of hours between a start and end time....but cannot get a MROUND function to work, to give me a decimal of half hour.

P27 = Start time
Q27 = End Time

1) This formula gives me good answer if the times are full hours...but rounds down to the nearest whole number if not
=IF(ISBLANK(P27),"", IF(HOUR(Q27-P27)>0,HOUR(Q27-P27),24))

2) When I try to use MROUND with 1/48 to show half hours, it shows a bad formula. i.e.
=IF(ISBLANK(P27),"", IF(HOUR(Q27-P27)>0,MROUND(HOUR(Q27-P27),1/48),24))

(Note, the 24 is at the end of the formula to account for 24 hours (12a - 12a) periods.

Any help is appreciated

 

[JAATL2014]

To further clarify:

if P27 = 3:00PM
and Q27 - 8:30PM

Answer should be 5.5. But it keeps coming up 5.

 

[AhoyNC]

try

Excel Workbook
	N	O	P	Q
27	5.5		3:00 PM	8:30 PM
Sheet

 

[Rick Rothstein]

Here is another way to write the fomula...

=IF(ISBLANK(P27),"",IF(Q27-P27>0,24*MROUND(Q27-P27,TIME(0,30,0))))

Note: You may have to manually set the cell format to "General" in order to see the floating point value.

 

[JAATL2014]

That did it, thank you

*	N	O	P	Q
27	5.5	*	3:00 PM	8:30 PM

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula N27 =IF(ISBLANK(P27),"", IF(Q27-P27>0,MROUND(Q27-P27,1/48)*24,24)) <tbody> </tbody>

<tbody>
</tbody>

That did it, thank you.

Excel tables to the web >> Excel Jeanie HTML 4 [/QUOTE]

 

[AhoyNC]

You're welcome. Either formula should work for you. Thanks for the feedback.