Rounding up/down to Nearest Quarter Hour

[Tacnola1]

Please see attached. I am trying to calculate to the nearest quarter hour/ either up or down.

I need to add the time from Cells E and F then have total time in Cell H. With this total, I need it to be rounded up or down to the nearest quarter hour (.15, .30, .45, .00)

Cell I would be used if the rounding can't be done in the same cell as H.

I also need the TOTAL Hours at the bottom to show the total hours to be shown as .25, .50, .75, .00 If this makes sense.

Thank you,
Teresa

 

[arthurbr]

Is this some kind of homework?

 

[Tacnola1]

No. It is a spreadsheet I am creating to calculate travel time.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Rounding Time to the Nearest Quarter Hour
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Alex Blakenburg]

Since you want the answer in hours and decimal of hours try this:
Note: The column is currently formatted as time ie hh:mm you will need to format it as Number or General.

=MROUND(IF(F16="",0,IF(F16>E16,F16-E16,$N$2-E16+F16))*24,0.25)

 

[Tacnola1]

I let this question sit on this site for roughly 24 hours before I posted to the other site, Excelforum.com. Due to the comical response of "Is this homework" I thought the algorithm was messed up and this post would fall into the realm of 'it has been answered. That is when I went to the other site for assistance.

 

[Peter_SSs]

That is when I went to the other site for assistance.

No problem posting there, it's just that we want to know about it & have a link to it. If you have read the link at the end of the rule you were directed to, you will know why.
You will have also seen that the other forum has a very similar rule regarding this issue. As far as I recollect it is the same for all the Excel forums that I visit.

 

[Alex Blakenburg]

@Tacnola1 - can you let me know if you tried the suggestion in my Post #5 and whether it does what you need ?

 

[Tacnola1]

@Tacnola1 - can you let me know if you tried the suggestion in my Post #5 and whether it does what you need ?

Thank you!! I was able to get it to work. Thank you for your assistance and time!

 

[Alex Blakenburg]

You're welcome. Glad we could help.