Count Days HH:MM using Network Days

[Steven1985]

Hi, I need a formula to count how many days hours & mins between 2 dates but included in the formula need to ensure it only counts business days.

Column A 02/09/2011 13:00

Column AO 02/09/2011 13:02

I need in Column AP to show 0 00:02

The formula I have tried using is - =IF(AO3="","",AO3-A3) which doesnt work as instead of showing 0 00:02 it shows 1 00:02. I dont want it to count as 1 day just 2 mins.

Once that has been worked out I need to include WORKDAYS formula to only count working days. I have created the list on 'Controls' Tab Column A10:A47.

Can anyone help merge these together to create the one formula?

Many Thanks

 

[Steven1985]

The formula =IF(AO3="","",AO3-A3) now works when I add a -1 after A3.

Can anyone help use this formula and add in the second part?

Thanks

 

[barry houdini]

OK, so are you counting 24 hours a day except for Saturdays, Sundays and holidays? What's in 'Controls' Tab Column A10:A47 - is that a list of holidays?

 

[GlennUK]

You are using a number format ( date format ) to show the days in your calculations, aren't you? Well, that's just showing the day in January 1900 that your number equates to ( according to the Excel date serial system ). It's not a display of the days for your number ( and note that adjusting by -1 may work at the moment, it'll all go wrong if your result has more than 31 days ).

Have your day calculations separate from your hours calculations, i.e. have the result split into 2 cells.

 

[Steven1985]

Hi Barry, yes im counting 24 hours but i need to exclude saturday, sundays and bank holidays. These dates to exclude are in the control tab A10:A47.

 

[barry houdini]

Glenn's right about using d hh:mm format. The d is actually day of the month not a duration so not only does it not go above 31 but it also changes based on whether you have 1904 date system set or not. I get 0 00:02 for your example, if you get 1 00:02 then that means you probably have 1904 date system.....

Assuming start and end dates/times are always on working days then try this formula

=NETWORKDAYS(A3,AO3,Controls!A$10:A$47)-1-(MOD(A3,1)>MOD(AO3,1))&" days "&TEXT(AO3-A3,"h:mm")

That will give you a text result like 3 days 5:31

or if you want a numeric result with which you can perform calculations then try this version

=IF(AO3="","",NETWORKDAYS(A3,AO3,Controls!A$10:A$47)-1+MOD(AO3,1)-MOD(A3,1))

Which I would recommend you format as [h]:mm to show 77:31....or in number format to show decimal days like 3.23....or you could still use d hh:mm....bearing in mind that the results can be wrong for periods over 31 days or when using 1904 date system

 

[Steven1985]

Thats works brilliant, thanks Barry. If I could just ask for one more addition if you can help me with this please?

Can we add to the beginning so that if the cell AO3 is blank then enter blank in the days taken field. At the moment if its blank it brings back error message #VALUE?

Thanks