Solved How to calculate working days and hours?

August 14, 2015 at 10:55:28
Specs: Macintosh
Hi, I am having some trouble with excel.

Project started: A2 cell: 1/1/15 8:00
Project ended: A3 cell: 7/1/15 10:00

I would like to find out how many working days and working hours and minutes it took for the project to complete. The answer should be 4 days and 2 hours. Could you please help me with the relevant formulas? I would like to be able to factor in holidays too. I have tried some formulas for networkdays and hours and minutes but unable to get the working hours (i.e. it should only calculate working hours from 9am - 6pm). PLEASE HELP ME!

Secondly, if I have multiple projects, how do I add up these working days and hours and minutes?

Thank you so much for your hope in advance.

See More: How to calculate working days and hours?

Report •

August 14, 2015 at 11:43:37
✔ Best Answer
This should get you what your looking for:

As written, you will first have to put your
Start Work Time in cell G2 = 9:00 AM
End Work Time in cell G3 = 6:00 PM

If you have any Holiday Dates they go in Cell H2 thru H6

Next enter this formula, it is very long, so you might want to just Copy/Paste it.

=IF(AND(INT(A2)=INT(A3),NOT(ISNA(MATCH(INT(A2),$H$2:$H$6,0)))),"0 days 0 hours", IF(INT(A2)=INT(A3),"0 days " & ROUND(24*(A3-A2),2)&" hours",MAX(NETWORKDAYS(A2+1,A3-1,$H$2:$H$6),0)+INT(24*(((A3-INT(A3))-(A2-INT(A2)))+($G$3-$G$2))/(24*($G$3-$G$2)))&" days "&MOD(ROUND(((24*(A3-INT(A3)))-24*$G$2)+(24*$G$3-(24*(A2-INT(A2)))),2),ROUND((24*($G$3-$G$2)),2))&" hours "))

If needed you can modify the cell locations,
just make sure you change them all in the formula.

See how it works for you.


Report •
Related Solutions

Ask Question