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:00I 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.

This should get you what your looking for: As written, you will first have to put your

Start Work Timein cellG2= 9:00 AM

andEnd Work Timein cellG3= 6:00 PMIf you have any

Holiday Datesthey go in CellH2thruH6Next 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.MIKE

Ask Your Question

Weekly Poll