• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel question: how to calculate working hours with some conditions

N

Natalia

New Member
Hello all!! I'm newbie here:) I've got the task re calculation of working hours with such conditions:
1. start date
2. end date
3. in the period from 1 Feb till 30 April there is 9 hrs working day, in the rest period 8 hrs working days.
e.g. i need to calculate working hours from 1 march 2014 till 30 May 2014. Please share with ideas^) Thanks!
 
Here's how I see it. We first figure out how many days are between the 2 dates, and multiply by 8. We then need to add an extra hour for each date that falls in Feb, Mar, or Apr. We'll use a SUMPRODUCT and MONTH function to figure that out. Confirm this formula as an array (Ctrl+Shift+Enter). Assumes start date is in A2, end date is in B2:
=(B2-A2)*8+SUMPRODUCT(1*(MONTH(ROW(INDIRECT(A2&":"&B2)))={2,3,4}))
 
Hi Luke ,

I think that since the SUMPRODUCT converts the expression within it to an array , the CTRL SHIFT ENTER is not required.

Narayan
 
Hi Luke if i put the Start Day as 1st april and end days as 30th april it should be 30 days.
So 30*9 comes as 270 but in the formula it is showing as 262. The difference of 8 hrs is coming.
 
Oops, you're correct, the formula should be inclusive of dates. Need to add 1 to beginning.

NARAYANK991 said:
Hi Luke ,

I think that since the SUMPRODUCT converts the expression within it to an array , the CTRL SHIFT ENTER is not required.

Narayan
Click to expand...

Narayan is also correct, I got mixed-up in my solutions. New formula:
=(B2-A2+1)*8+SUMPRODUCT(1*(MONTH(ROW(INDIRECT(A2&":"&B2)))={2,3,4}))
 
Hmm. While we could use NETWORKDAYS to calculate number of days and then multiply by 8, it's the extra days in Feb-Apr that's going to cause a headache, especially over multiple years.
 
Hello Natalia,

Try this formula,

=SUMPRODUCT(LOOKUP(MONTH(WORKDAY(A2-1,ROW(INDEX(A:A,1):INDEX(A:A,NETWORKDAYS(A2,B2,Holidays))),Holidays)),ROW(A$1:A$12),{8,9,9,9,8,8,8,8,8,8,8,8}))

Hope some one will have a shorter formula.
 
Hi,
Here is another (longer) approach you could try, based on the following assumptions:
The time period for 9 hour days could change from Feb-Apr to something else
StartMonth=Feb 1, 2013 (entered as a valid date. The year does not matter.)
EndMonth=Apr 30, 2013 (entered as a valid date. The year does not matter.)
StartDate=Mar 1, 2014
EndDate=May 30, 2014

=NETWORKDAYS(MAX(DATE(YEAR(StartDate), MONTH(StartMonth), DAY(StartMonth)), StartDate), MIN(DATE(YEAR(EndDate), MONTH(EndMonth), DAY(EndMonth)), EndDate)) + NETWORKDAYS(StartDate, EndDate)*8

returns 563 hours (same as with Haseeb's formula)

With the above approach, you could calculate the 9 hour days for partial months also by changing StartMonth and EndMonth. (For example, they could be Feb 15, 2013 to Mar 15, 2013.)

Please test before using.

Cheers,
Sajan.
 
Hi Sajan ,

Nice to see you back in action ; have you verified your formula with a period ( StartDate , EndDate ) spanning multiple years ?

Narayan
 
Hi Narayan,
No I have not checked the formula against multiple years. What is the expected result for multiple years?

If StartDate=Mar 1, 2014 and EndDate=Dec 31, 2016, are the 9 hour workdays only applicable to the given months, or do they indicate the start and end months? For example, Feb of 2014 to Apr of 2015 versus Feb,2014-Apr,2014 and Feb,2015-Apr,2015, etc.

-Sajan.
 
Hi Sajan ,

sthomas said:
Hi Narayan,
No I have not checked the formula against multiple years. What is the expected result for multiple years?

If StartDate=Mar 1, 2014 and EndDate=Dec 31, 2016, are the 9 hour workdays only applicable to the given months, or do they indicate the start and end months? For example, Feb of 2014 to Apr of 2015 versus Feb,2014-Apr,2014 and Feb,2015-Apr,2015, etc.

-Sajan.
Click to expand...
I cannot be sure , but I think it would be the latter i.e. 9 hour workdays would be applicable only in the months of February , March and April every year ( on the lines of Daylight Saving Time ? )

Narayan
 
Hi Narayan,
I have not had a chance to validate the following formula properly but am posting it any way, in case someone else will get a chance to do so. I tried a few time periods within a year, and that seemed to work consistently with my previous formula. However, since the previous formula does not work across years, I will need to find a different way to validate the new formula for periods that span multiple years.

=SUMPRODUCT(CHOOSE(1+(ABS(MMULT(SIGN(("1"&TEXT(WORKDAY(StartDate,ROW(INDEX(A:A,1):INDEX(A:A,NETWORKDAYS(StartDate,EndDate)))),"mmdd"))-CHOOSE({1,2},("1"&TEXT(OTPeriodStart,"mmdd")),("1"&TEXT(OTPeriodEnd,"mmdd")))), {1;1}))<2), 8,9))

I borrowed the WORKDAY function from Haseeb's formula since that was a new one for me!

The concept that I pursued is that if I subtract the "mmdd" value for the timeperiod from the Overtimeperiod, then I should get pairs of negative, zero, or positive values. The only valid pairs should be those that result in absolute sum of 0 or 1.

If no one else gets a chance to test this hypothesis, I will check it out tomorrow.

-Sajan.
 
sthomas said:
Hi Narayan,
I have not had a chance to validate the following formula properly but am posting it any way, in case someone else will get a chance to do so. I tried a few time periods within a year, and that seemed to work consistently with my previous formula. However, since the previous formula does not work across years, I will need to find a different way to validate the new formula for periods that span multiple years.

=SUMPRODUCT(CHOOSE(1+(ABS(MMULT(SIGN(("1"&TEXT(WORKDAY(StartDate,ROW(INDEX(A:A,1):INDEX(A:A,NETWORKDAYS(StartDate,EndDate)))),"mmdd"))-CHOOSE({1,2},("1"&TEXT(OTPeriodStart,"mmdd")),("1"&TEXT(OTPeriodEnd,"mmdd")))), {1;1}))<2), 8,9))

I borrowed the WORKDAY function from Haseeb's formula since that was a new one for me!

The concept that I pursued is that if I subtract the "mmdd" value for the timeperiod from the Overtimeperiod, then I should get pairs of negative, zero, or positive values. The only valid pairs should be those that result in absolute sum of 0 or 1.

If no one else gets a chance to test this hypothesis, I will check it out tomorrow.

-Sajan.
Click to expand...
Hi Sajan

Perfect ; but I would go with Haseeb's formula which seems intuitive.

Of course , your formula is more general in nature , because your OTPeriodStart and OTPeriodEnd are dates , not months.

Narayan
 
We can shorter lookup_vector & vector_results to {1;2;5},{8;9;8}

=SUMPRODUCT(LOOKUP(MONTH(WORKDAY(A2-1,ROW(INDEX(A:A,1):INDEX(A:A,NETWORKDAYS(A2,B2,Holidays))),Holidays)),{1;2;5},{8;9;8}))
 
Hi, Natalia!

Tweaking a bit Luke M's formula I arrived to this shorter and maybe simpler one:
=DIAS.LAB(A2;B2)*8+SUMAPRODUCTO(((MES(FILA(INDIRECTO(A2&":"&B2))))>=2)*((MES(FILA(INDIRECTO(A2&":"&B2))))<=4)) -----> in english: =NETWORKDAYS(A2,B2)*8+SUMPRODUCT(((MONTH(ROW(INDIRECT(A2&":"&B2))))>=2)*((MONTH(ROW(INDIRECT(A2&":"&B2))))<=4))
which I think it works over multiple years, normal or leap.

Hope it helps.

Regards!
 
SirJB7 said:
Hi, Natalia!

Tweaking a bit Luke M's formula I arrived to this shorter and maybe simpler one:
=DIAS.LAB(A2;B2)*8+SUMAPRODUCTO(((MES(FILA(INDIRECTO(A2&":"&B2))))>=2)*((MES(FILA(INDIRECTO(A2&":"&B2))))<=4)) -----> in english: =NETWORKDAYS(A2,B2)*8+SUMPRODUCT(((MONTH(ROW(INDIRECT(A2&":"&B2))))>=2)*((MONTH(ROW(INDIRECT(A2&":"&B2))))<=4))
which I think it works over multiple years, normal or leap.

Hope it helps.

Regards!
Click to expand...

Hi Pablo ,

I am not so sure ; using NETWORKDAYS with A2 and B2 gives only one part of the looked-for result ; the second part is not using NETWORKDAYS to arrive at the extra one hour only for the NETWORKDAYS between February 1 and April 30 ; it will add the extra 1 hour for all the days between February 1 and April 30 , which may not be what the OP was looking for.

We can wait for the OP to confirm.

Narayan
 
Hi, NARAYANK991!
No need to wait for OP's test, you're right about the 2nd part, it requires and additional condition for working days. I'll try to fix it up later, now dinning and in 26' I have my midnight conference with NCIS.
Regards!
 
You must log in or register to reply here.
Back
Top