• 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.

Working days and hours only

M

malbarki

Member
Hey guys,

I need your help with the following

If I have start date and time in A1 and end date and time in B1, and i want to count the minutes deference between them how can i do it. Taking in consideration that only working days & hours are to be calculated which mean you'll have to exclude the weekends and non working hours

Weekend = Friday and Saturday
Working hours = From 8:00 AM to 17:00 PM
A1= 07/09/2013 9:30:00 AM
B1= 13/09/2013 4:00:00 PM
Expected Result from the given dates

Start Date and Time 07/09/2013 9:30:00 AM = Excluded
08/09/2013 = 9 Hours
09/09/2013 = 9 Hours
10/09/2013 = 9 Hours
11/09/2013 = 9 Hours
12/09/2013 = 9 Hours
End Date and Time 13/09/2013 4:00:00 PM = Excluded

Final Result = 45 Hours
----------------------------------------------------------
Another Example

Start Date and Time 18/09/2013 7:00:00 AM = 9 Hours << as anything before 8AM should be excluded
19/09/2013 = 9 Hours
20/09/2013 = Excluded
21/09/2013 = Excluded
End Date and Time 22/09/2013 7:00:00 PM = 9 Hours << as anything after 5PM should be excluded

Final Result = 27 Hours

** I'm using Excel 2007

Thanks in advance
 
I believe this will do it:
=SUMPRODUCT((WEEKDAY(ROW(INDEX(A:A,StartDate):INDEX(A:A,EndDate)))<6)*LengthWorkDay)-
(WEEKDAY(EndDate)<6)*MAX(0,MIN(LengthWorkDay,EndTime-MOD(EndDate,1)*24))-
(WEEKDAY(StartDate)<6)*MAX(0,MIN(LengthWorkDay,MOD(StartDate,1)*24-StartTime))
Key:
StartDate - Beginning date
EndDate - Ending date
LengthWorkDay - Number of hours in a work day
EndTime - End of work day, in military hours (e.g., 5pm = 17)
StartTime - Start of work day, in military hours (e.g., 7am = 7)

The first line calculate total hours in workdays for Sun-Thursday, 9 hrs per day. We then need to subtract any hrs not used in StartDate and EndDate. This is the reason for lines 2 and 3 in formula.
 
Last edited:
Hey Luke, thank you very much for the prompt reply one small clarification if I may. In the first line may I know what's the "A:A" range for exactly..
 
Hi ,
This is a standard construct to get an array of values ; suppose we want to get an array of values as follows :

{5;6;7;8;9;10;11;12}

We would use the construct :

=ROW($5:$12)

We can use the above construct only because the starting point , which is 5 in this case , and the end point , which is 12 in this case are static values.

Suppose now we had the value 5 in cell A1 , and the value 12 in cell B1 ; we cannot use the construct :

=ROW($A$1:$B$1)

The above formula will return only 1 row , since the range A1:B1 consists of just 1 row.

When we need to generate an array of values given this situation , we resort to the following construct :

=ROW(INDIRECT($A$1&":"&$B$1))

In this case , even this is not possible ! This is because the values in StartDate and EndDate are not integer values , which dates would be ; a combination of date and time is a decimal number , where the integer portion is the date , and the decimal portion is the time.

Luckily for us , the INDEX function ignores decimals ! Thus instead of getting the integer portion of the date + time combination , we pass the decimal number as it is to the INDEX function ; the function uses the integer portion alone.

Thus the construct :

=ROW(INDEX(A:A,StartDate):INDEX(A:A,EndDate))

is the same as :

=ROW(INDIRECT(INT(StartDate)&":"&INT(EndDate)))

There is a slight twist here ; the formula =INDEX(A:A,StartDate) will return the data to be found in the cell A5 , assuming StartDate = 5 ; however , the formula =ROW(INDEX(A:A,StartDate)) returns an array of one number 5 i.e. {5}.

When we combine the two StartDate and EndDate , we get an array of numbers between 5 and 12 viz. {5;6;7;8;9;10;11;12}

Narayan
 
bobhc said:
Good day malbarki

The A:A in Luke M's code means the whole of column A
Click to expand...
Yah that I know, but does it mean I have to list all my start dates in column A and all my end dates in column B... If that's the case should I change the second A range to B

INDEX(A:A,StartDate):INDEX(A:A,EndDate)

INDEX(A:A,StartDate):INDEX(B:B,EndDate)

or did I get it all wrong :oops:
 
malbarki said:
Yah that I know, but does it mean I have to list all my start dates in column A and all my end dates in column B... If that's the case should I change the second A range to B

INDEX(A:A,StartDate):INDEX(A:A,EndDate)

INDEX(A:A,StartDate):INDEX(B:B,EndDate)

or did I get it all wrong :oops:
Click to expand...


You should just leave it as A:A, but it doesn't really matter. The contents of the column are not what's important, I'm just using it to generate an array of numbers/dates. If you read Narayan's post above, I think he does a wonderful job of breaking it down.

So, in short, you don't need to put you start dates in col A and end dates in col B, they could be whatever. The only cell references you need to update are the "StartDate" and "EndDate", either by replacing them with cell references or setting up your own Named Ranges.
 
@malbarki
Hi!
If Skywalker doesn't slap certain part of my body with his laser sword I'll add this:
Instead of using column A (A:A) or B (B:B) it'd be the same if he used column ZY (ZY:ZY), since the important part is that it's used to generate the above described array and it isn't referencing any cell value of the used column.
Hope it helps.
Regards!
 
Guys you're amazing it works like magic thank you very much

One more question if I may, is it possible to exclude holidays if I define them somewhere?
 
Hi, malbarki!
Am I wrong or the "<6" in Luke M's formula for the WEEKDAY function excludes Friday (6) and Saturday (7) since week start on Sunday (1)?
Regards!
 
Hi malbarki,
We can, just adds another array check.
=SUMPRODUCT((WEEKDAY(ROW(INDEX(A:A,StartDate):INDEX(A:A,EndDate)))<6)*9*ISNA(MATCH(ROW(INDEX(A:A,StartDate):INDEX(A:A,EndDate)),HolidayRange,0)))-
(WEEKDAY(EndDate)<6)*ISNA(MATCH(EndDate,HolidayRange,0))*MAX(0,MIN(9,17-MOD(EndDate,1)*24))-
(WEEKDAY(StartDate)<6)*ISNA(MATCH(StartDate,HolidayRange,0))*MAX(0,MIN(9,MOD(StartDate,1)*24-8))

Just as a heads up, things get much easier in XL 2010+ because then you have the WORKDAY.INTL function which lets you change the weekend.

@SirJB7
Correct, the "<6" excluded Friday and Saturdays.
 
Luke M said:
Hi malbarki,
We can, just adds another array check.
=SUMPRODUCT((WEEKDAY(ROW(INDEX(A:A,StartDate):INDEX(A:A,EndDate)))<6)*9*ISNA(MATCH(ROW(INDEX(A:A,StartDate):INDEX(A:A,EndDate)),HolidayRange,0)))-
(WEEKDAY(EndDate)<6)*ISNA(MATCH(EndDate,HolidayRange,0))*MAX(0,MIN(9,17-MOD(EndDate,1)*24))-
(WEEKDAY(StartDate)<6)*ISNA(MATCH(StartDate,HolidayRange,0))*MAX(0,MIN(9,MOD(StartDate,1)*24-8))

Just as a heads up, things get much easier in XL 2010+ because then you have the WORKDAY.INTL function which lets you change the weekend.

@SirJB7
Correct, the "<6" excluded Friday and Saturdays.
Click to expand...

Hello Luke,

Again it works like magic, thank you very very much

As for my XL version, the thing is I've been asking for an upgrade since forever but my organization won't approve it coz if they will go with V.10 they have to change it for everybody in the organization and they're saying there is no need for that so I'm stuck with V.7 :(
 
No worries malbarki. My company is of the same position. Someday we'll get there..probably in 2020. :p
 
Ha ha, very nice SirJB7. On my own machine at home, I have done such a thing, but the IT police at work keep me from doing to much to my machine. :(
 
Hello everybody

Can you please check why the formula provided by Luke in column "C" is not working..
 

Attachments

  • Test.xlsx
    13.9 KB · Views: 22
Hi malbarki,

Its working just fine. just see the attached screenshot. I will suggest you to enter the formula manually and just not copy n paste.

Screen Shot 2013-12-22 at 20.45.28.png

Regards!
 
Hi malbarki,

Just see the attached file. If the results are OK.

I had use below formula.

=(SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=7)+(ROW(INDIRECT(INT(A2)&":"&INT(B2)))=$E$2),0,1)*9)-((WEEKDAY(B2,1)<6)*(INT(B2)<>$E$2)*(17-(MOD(B2,1)*24)))-((WEEKDAY(A2,1)<6)*(INT(A2)<>$E$2)*((MOD(A2,1)*24)-8)))*60

Enter with Ctrl+Shift+Enter as this is an Array formula.

Just see if this work otherwise advise if you have any issue.

Regards!
 

Attachments

  • Work Day n Time(modified).xlsx
    12.7 KB · Views: 18
Last edited by a moderator:
Somendra Misra said:
Hi malbarki,

Just see the attached file. If the results are OK.

I had use below formula.

Code: 
=(SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=7)+(ROW(INDIRECT(INT(A2)&":"&INT(B2)))=$E$2),0,1)*9)-((WEEKDAY(B2,1)<6)*(INT(B2)<>$E$2)*(17-(MOD(B2,1)*24)))-((WEEKDAY(A2,1)<6)*(INT(A2)<>$E$2)*((MOD(A2,1)*24)-8)))*60

Enter with Ctrl+Shift+Enter as this is an Array formula.

Just see if this work otherwise advise if you have any issue.

Regards!
Click to expand...

It's not working for some entries as you can see in the attached in red
 

Attachments

  • Work_Day_n_Time(modified)(1).xlsx
    15.2 KB · Views: 12
@oldchippy ...no thats right as per the logic both end time are after 17:00 so they are not counted and their starting time also after 17:00 so they are also not counted so there is one full day which is counted 21/11/2013 do 9 hours x 60 mins = 540 mins.
Regards!
 
You must log in or register to reply here.
Back
Top