Count overnight resources from PM to AM shift.

Anonymous
2023-06-08T22:34:24+00:00

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!

Microsoft 365 and Office | Excel | For home | Windows

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.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102.6K Reputation points Volunteer Moderator
    2023-06-10T07:31:29+00:00

    Hi,

    Enter this formula in cell C2

    =B2-A2+(A2>B2)

    Hope this helps.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-06-09T00:37:37+00:00

    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

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2023-06-09T01:45:08+00:00

    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

    Was this answer helpful?

    0 comments