Workdays to be counted

[Robert110]

Hi,

I have the attached table where I need to work out the number of hours and mintues it take to complete a process from the start time and the end time.
However, some of these days will fall on a weekend or a public holliday, so I only need the workdays to be counted.
I know there is a function called Workdays, but I am having trouble using it.
I also just tried subtracting the cells from each other and setting the cell to format hh:mm, but it returns "#########"

If any one can see how I can get this to count the workdays It would really help me out.

Thanks in advance,

Rob

[Dave H9]

Try the following as "workdays" is not a function -

=NETWORKDAYS(Start date,end date)

Hope that helps

Dave H

[Robert110]

Hi Dave,

Thanks very much for the help.
One slight problem is that I wanted it show it in hours and minutes, do you know if that is possible?
When I apply the number format hh:mm to the cell I get 00:00. but if I change it to general. then it counts the the number of days only.

Thanks

Rob

[Kevin UK]

Hi Robert110

Try the following, assuming your data is in A1:C14, in D1 and copy down.

Please Login or Register  to view this content.

Put your holiday range into the NETWORKDAYS function. Format the cells as "[h]:mm", without the quotes.

[Robert110]

Hi thanks Kevin,

Maybe I am doing some thing wrong here, but it seems to return the same time that is in column C.

for example

Feb-12 (Column A)
29/02/2012 00:00 (Column B)
02/03/2012 14:53 (Column C)
14:53 (Column D)

Sorry it is like that, I couldn't work out how to put it in a table!

but it should return 38 hours 53mins (24 hours for the whole day March 1st 2012 plus 14 hours 53 mins for 2nd Feb 2012)

Did I miss something out somewhere?

Thanks

Rob

[daddylonglegs]

but it should return 38 hours 53mins (24 hours for the whole day March 1st 2012 plus 14 hours 53 mins for 2nd Feb 2012)

Why aren't you counting 29th February? In Excel terms 29/2/2012 at 00:00 is the start of that day

Will your start times always be at 00:00?

[Robert110]

Hi,

Sorry I wasn't clear, start time will always start at the first minute of each month.
I can see that my start day and time should be 1st March 2012 00:00, but even with this change I still can not get the right hours and minute count

Rob

[daddylonglegs]

Kevin's formula should work for you, although with the start time always at midnight you can simplify a small amount to

=NETWORKDAYS(B1,C1)-1+MOD(C1,1)

....but either way you need to custom format the result cell as [h]:mm - the square brackets indicate "elapsed time" and you'll see results over 24:00

[Robert110]

Hi Dave H, Kevin UK and daddylonglegs.

Thanks very much, I think because my region setting was set up Danish, I had some problems, but now it is in English it work perfectly.

Thanks again guys

Rob

[Kevin UK]

Hi Robert110

No problem and thanks for the feed back.