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)*24Any ideas on how I can capture all the data?

Haven't looked at your formula too closely,

but the usual solution is to format the cell as: [H]:MMThe square brackets lets Excel know your going past Midnight.

MIKE

Ask Your Question

Weekly Poll