info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Formula to determine the number of work hours per month
33 views
Skip to first unread message
sicelo matambo
Jun 7, 2021, 5:46:49 AM6/7/21
to
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
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
Claus Busch
Jun 9, 2021, 4:53:47 AM6/9/21
to
Hi,
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)+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(2021,A1,1)&":"&EOMONTH(DATE(2021,A1,1),0))),2)=5)*8)
Regards
Claus B.
--
Windows10
Microsoft 365 for business
Then try:
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(2021,A1,1)&":"&EOMONTH(DATE(2021,A1,1),0))),2)<5)*24)+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(2021,A1,1)&":"&EOMONTH(DATE(2021,A1,1),0))),2)=5)*8)
Regards
Claus B.
--
Windows10
Microsoft 365 for business
0 new messages