Want to calculate Total Working hours(Excluding Saturday n Sunday i.e. weekends)

[Mayur Gijam]

Hello All,

I have came across a situation where I am not able to understand how to retrieve desired output using excel.

In cell A1 I have given a cell with information about Reported Date or Start Date i.e. 4/11/2014 10:23:00 PM
In cell B1 I have given a cell with information about End Date date i.e. 4/19/2014 4:06:00 PM

Now in cell C1 I want to calculate Total Working hours where working hours start from 9 am and ends on 5 pm (Excluding Saturday n Sunday i.e. weekends) i.e. in above situation its 41:37 hours...

This I want to achieve using formula....

Please guys help me out... Thanks in advance

 

[Luke M]

You're example does not follow your stated rules. If you start work at 10:23 PM, but working hours are 9am to 5pm, you should get no credit for working after 5pm. Answer to above should be 40 hours. (8 hours days on 4/14 - 4/18).

Formula to solve your problem:
=(SUMPRODUCT((WEEKDAY(ROW(INDEX(A:A,StartDate):INDEX(A:A,EndDate)),2)<6)*8)-
(WEEKDAY(EndDate,2)<6)*MAX(0,MIN(8,17-MOD(EndDate,1)*24))-
(WEEKDAY(StartDate,2)<6)*MAX(0,MIN(8,MOD(StartDate,1)*24-9)))/24

Previous thread showing how this works:
http://chandoo.org/forum/threads/working-days-and-hours-only.12185/#post-71438

 

[Mayur Gijam]

Hello Luke...
Yes you are correct my given example will get 40 hours as output
I tried your given formula... but I was unable to get desired output... I am getting 1.66666666666667

Can you please have a look at excel attachment in this comment... I have included my given sceario with some additional cases

 

[Luke M]

change the format of the cells to:
[h]:mm
The cells are currently in general format, and since dates/time are stored as integers & decimals in XL, that is what you are seeing. E.g., 1.6666 = 1 day + 2/3 of a day = 24 hrs + 16 hrs = 40 hrs.