Formula to determine the number of work hours per month
Hi,
Am Mon, 7 Jun 2021 02:46:45 -0700 (PDT) schrieb sicelo matambo:
I am trying to figure out the summary of work hours for a month. The problem is that we work 24hrs on Mon to Thu and on Fridays we only work 8hrs.
Therefore a short month will have less hours than a long month and a month with more Fridays will have less hours than other months.
Please help me with a formula which determines the number of work hours per month as
per above schedule.
I have named a certain cell "Month_no". The number in the cell is changing according to a macro. The formula should refer to the name: Month_no and determine the number of hours for that specific month.
the number of the month in A1.
Then try:
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(2021,A1,1)& ":"&EOMONTH(DATE(2021,A1,1),0))),2)<5)*24)+SUMPROD UCT((WEEKDAY(ROW(INDIRECT(DATE(2021,A1,1)&":"&EOMO NTH(DATE(2021,A1,1),0))),2)=5)*8)
Regards
Claus B.
--
Windows10
Microsoft 365 for business
|