NETWORKDAYS formula CHECK!! Please

[coded]

Hi all,

First time user, short term lurker! I'm not sure if this is the right place or not but I'd like to get a formula check as a solution to the following issue:

I'm trying to sum the total number of networkdays and hours (so totaling only those days; hours that fall between monday - friday), between two date:time filled columns in Excel 2010.

I've run through the search function and found 'daddylonglegs' has answered this question a few times before (just y'know, once or twice.. but I want to make sure MY work around is applicable to MY situation (I'm a special snowflake dammit.)

Given A2 Contains the Start Date & Time
and B2 Contains the End Date & Time

Please also note that this data cannot have an End Date & Time that comes before a Start Date & Time (Step 2 cannot happen before step 1's completion)

So then the summation formula would be...

=IF(NETWORKDAYS(A2,B2)-1 =0, mod(B2,1)-mod(A2,1),(NETWORKDAYS(A2,B2)-1)+MOD(B2,1)-MOD(A2,1)))

Look familiar?

Anyway, I added in the IF statement to make sure I capture just the hours between Start Date and End Date --- if the Start Date & Time and End Date & Time are on the same day (happens quite a bit within my data set).

Please advise!

Thanks all and I hope to become a very active member on these boards!

[daddylonglegs]

You shouldn't need the IF function

If the start and end dates are always Monday to Friday it's sufficient to use this version

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)

That will work, even if A2 and B2 are on the same day

.......or do you have start/end dates on weekends?

[coded]

Hiya DLL!

Thank you for your quick response!

I looked over the data and found a few Start/ End dates that occur on Sat/Sun... AND my previous formula forgot to account for Holidays between Start and End dates. (my bad!)

So basically these dates & times are usually created during the work week, with a few exceptions...

So how do I utilize the networkdays.intl function while accounting for Sat/Sun start/end dates??

Thank you for your time!

[daddylonglegs]

This version will only count MF, non-holiday time, and allows start/end times to be any time (even on a holiday) - assuming holiday dates in H2:H10

=NETWORKDAYS(A2,B2,H$2:H$10)+NETWORKDAYS(B2,B2,H$2:H$10)*(MOD(B2,1)-1)-NETWORKDAYS(A2,A2,H$2:H$10)*MOD(A2,1)

[coded]

Wow! Thanks, DLL!

So if I'm reading this correctly the first portion of the formula will calculate the NETWORKDAYS: M:F, given non-holiday time; the second part calculates the hour difference between the end date's hours and the start date's hours..

Awesome!! But quick question.. what if the end date hours are earlier than the start date hours? Say it was ordered at 3pm, and delivered at 8 am - wouldn;t the serial number for 8am be less than the 3pm causing it to subtract time?

Also, should it include a final -1 to remove the instances when A2 and B2 are the same day and counted as 2 by the first part of the formula?

Sorry about all the questions, but I like to know what I'm incorporating!!! Either way thank you SO much for your help!

[daddylonglegs]

.. what if the end date hours are earlier than the start date hours? Say it was ordered at 3pm, and delivered at 8 am - wouldn;t the serial number for 8am be less than the 3pm causing it to subtract time?

Yes, but in that scenario the 8 AM delivery must be on a later date so you will get a negative time value subtracted from a whole number of days, e.g. if the date was next working day you'd get 1 day (24:00) - 3PM (15:00) + 8 AM (8:00) and that sums to 17:00 which is the correct number of hours

Also, should it include a final -1 to remove the instances when A2 and B2 are the same day and counted as 2 by the first part of the formula?

Yes, the -1 is still there as per the first formula I suggested but it's now incorporated in the (MOD(B2,1)-1) part, so for same (working) day the first NETWORKDAYS gives you 1 and then the -1 sets that back to zero and you effectively just get the time difference.

The formula should work for any time range as long as B2 > A2 - If you have holiday dates to exclude you can do that too by inserting the holiday range in each of the NETWORKDAYS functions

[coded]

Amazing!! Thank you, DLL!!!

You rock!