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

Do you think facial recognition technology should be regulated?

Discuss in The Lounge

Poll History