Good to know
Cell E1:H8
Cell A1:C4
=SUMPRODUCT(INT((WEEKDAY(A2-{1;2;3;4;5;6;7})+INT(B2)-INT(A2))/7),H$2:H$8)-MEDIAN(0,MOD(A2,1)-INDEX(F$2:F$8,WEEKDAY(A2)),INDEX(H$2:H$8,WEEKDAY(A2)))-MEDIAN(0,INDEX(G$2:G$8,WEEKDAY(B2))-MOD(B2,1),INDEX(H$2:H$8,WEEKDAY(B2)))
Reference: link (+Excel file)
Cell E1:H8
| Day | Start | End | Hours |
| Sunday | 0:00 | ||
| Monday | 08:00 | 18:00 | 10:00 |
| Tuesday | 08:00 | 18:00 | 10:00 |
| Wednesday | 08:00 | 18:00 | 10:00 |
| Thursday | 08:00 | 18:00 | 10:00 |
| Friday | 08:00 | 18:00 | 10:00 |
| Saturday | 08:00 | 13:00 | 5:00 |
Cell A1:C4
| Start | End | Hours |
| 2009-02-14 Sat 08:00 | 2009-02-15 Sun 13:00 | 05:00 |
| 2002-09-09 Mo 12:00 | 2012-09-12 Thu 03:00 | 26:00 |
| 2001-01-21 Sun 08:00 | 2001-01-22 Mon 15:51 | 07:51 |
=SUMPRODUCT(INT((WEEKDAY(A2-{1;2;3;4;5;6;7})+INT(B2)-INT(A2))/7),H$2:H$8)-MEDIAN(0,MOD(A2,1)-INDEX(F$2:F$8,WEEKDAY(A2)),INDEX(H$2:H$8,WEEKDAY(A2)))-MEDIAN(0,INDEX(G$2:G$8,WEEKDAY(B2))-MOD(B2,1),INDEX(H$2:H$8,WEEKDAY(B2)))
Reference: link (+Excel file)
Inga kommentarer:
Skicka en kommentar