Count overnight resources from PM to AM shift.

Question

Count overnight resources from PM to AM shift.

Anonymous

I'm trying to count resources based on time frame. Countif formula works if time falls from AM to PM however if it's an overnight shift it does not count the resource. Any insight on how I can capture overnight resources from PM to AM shift is appreciated!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

3 answers

Answer 1

Hello AlenaNR,

Basically, 00:00 is valued as 0 and 12:00 is valued as 0.5. Time counts up the hours and restarts at 0 when you reach midnight. The way around this is to use dates along with the times. Each day is valued as 1. Adding 1 to a date results in the following day.

Example: Now() results in something like this, where the whole numbers is the number of days and the decimals are the hours/minutes/seconds. 45085.77027

Set your formula to add 1 when the end time is past midnight, but before the start time.

Example: =(EndTime - IF(StartTime>EndTime,1,0)) - StartTime

Answer 2

Hello ,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

One way to count resources for an overnight shift from PM to AM is by using an IF formula. You can enter the following formula: =IF (End Time < Start Time, End Time + 1, End Time) - Start Time 1. This formula checks if the end time is less than the start time (which would be the case for an overnight shift), and if it is, it adds 1 to the end time to account for the shift going into the next day. Then it subtracts the start time from the end time to calculate the duration of the shift.

Best Regards, IBHADIGHI

Answer 3

Ashish Mathur 100.8K Volunteer Moderator

Hi,

Enter this formula in cell C2

=B2-A2+(A2>B2)

Hope this helps.

Share via

Count overnight resources from PM to AM shift.

3 answers