Share via

EXCEL worktime concern

Anonymous
2024-07-30T02:57:07+00:00

Hi Snow Lu,

Thanks for your help yesterday: Count work time in Excel - Microsoft Community

However we still find some issues while using the formula, please check:

Can you help us with this scenario?

Microsoft 365 and Office | Excel | For business | 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

Answer accepted by question author

Anonymous
2024-07-30T06:20:09+00:00

=LET(b,IF(WEEKDAY(A2,2)=6,INT(A2)-6/24,IF(WEEKDAY(A2,2)=7,INT(A2)-30/24,IF(A2-INT(A2)<9/24,INT(A2)+9/24,IF(A2-INT(A2)>18/24,INT(A2)+18/24,A2)))),

a,IF(WEEKDAY(B2,2)=6,INT(B2)-6/24,IF(WEEKDAY(B2,2)=7,INT(B2)-30/24,IF(B2-INT(B2)<9/24,INT(B2)+9/24,IF(B2-INT(B2)>18/24,INT(B2)+18/24,B2)))),

MAX(0,18/24-MAX(9/24,(b-INT(b))))*24+MAX(0,(MAX(9/24,(a-INT(a))))-9/24)*24+(NETWORKDAYS(b,a)-2)*9)

Was this answer helpful?

1 person found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest