Business days without using NETWORKDAYS

[Davers]

Is it possible to calculate Business days without using NETWORKDAYS? If so how...I have a reporting program that doesn't use VBA and doesn't have a built in Business day calc...I need to calculate the number of days between 2 dates minus weekends. I'd settle for excluding just weekends for now...but ultimately I'd like it to be able to exclude a list of Holidays as well...

Thanks for any help!!

Dave

 

[barry houdini]

To exclude weekends you could use

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+B2-A2)/7))

where A2 is start date and B2 is end date....or....

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

....which can be amended to exclude holidays

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),H$2:H$10,0)))

where H2:H10 contains a list of holiday dates

 

[Davers]

Thanks Barry, I'll see if I can get that to work in my reporting tool!!!

Have a good weekend!

Dave

 

[Davers]

Hi again Barry...would you be able to explain to me in plain english what the below formula is doing?

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+B2-A2)/7))


Thanks,

Dave

 

[barry houdini]

Breaking it down a little....

=INT((WEEKDAY(A2-2)+B2-A2)/7)

Will count the number of Mondays between A2 and B2 (inclusive). You can replace the bold 2 in that formula depending on what day you wish to count - Sunday = 1 up to Saturday = 7.

to count all weekdays, therefore, you can use {2,3,4,5,6}, i.e. Monday to Friday and SUM those.

Do you want a more detailed explanation of

=INT((WEEKDAY(A2-2)+B2-A2)/7)

 

[Davers]

Yes Barry please...I'm trying to wrap my mind around how it's getting the correct result...I'm sorry to be such a pain... :-/

Thanks again,

Dave

 

[barry houdini]

If you consider a situation where B2 is 10 days later than A2, within that range there will be either 1 Monday or 2 depending on the day of the week of A2. If A2 is a Tuesday, Wednesday or Thursday there'll be only 1 Monday, otherwise 2.

E.g. if A2 is 21st April 2006 and B2 is 1st May 2006

=INT((WEEKDAY(A2-2)+B2-A2)/7)

becomes

=INT((WEEKDAY(19th April)+10)/7)

which is equal to

=INT((4+10)/7) = 2

whereas if both dates were a day earlier you'd get

=INT((WEEKDAY(18th April)+10)/7)

=INT((3+10)/7) = 1