Formula to determine the number of work hours per month
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. Thanks for helping |
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 |
All times are GMT +1. The time now is 08:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com