Solved Excel formula to calculate time in quarter hour increments

August 15, 2017 at 11:52:16
Specs: Windows 7
Hi Everyone,

I am trying to calculate time in quarter hour increments, but i have run into a problem, when the time spans an overnight. I can get the time capture for the overnight hours (11p-7:30a), but my formula won't calculate at 8.5, it gives me hours and minutes. I am using =IF(B2<A2,B2+1,B2)-A2

I can get the following formula to work as long as the time doesn't cover the overnight (11p-7:30a) or a 12-12 shift.
=TIME(HOUR(SUM(B2-A2)),MROUND(MINUTE(SUM(B2-A2)),15),0)*24

Any ideas on how I can capture all the data?


See More: Excel formula to calculate time in quarter hour increments

Reply ↓  Report •

#1
August 16, 2017 at 06:26:48
✔ Best Answer
Haven't looked at your formula too closely,
but the usual solution is to format the cell as: [H]:MM

The square brackets lets Excel know your going past Midnight.

MIKE

http://www.skeptic.com/


Reply ↓  Report •
Related Solutions


Ask Question