NETWORKDAY for a 4 day week

[MaryAnd]

I'm looking for a formula or UDF to calculate the net working days on a 4 day work week in Excel 2007. I'm attempting to calculate how late or early a project is so I need it to display +/-. Right now I'm using =NETWORKDAYS(L10,E10,0) where L10 is the Expected Completion and E10 is the Contract Completion. I know I could list all the Fridays as holidays but ideally I need something more flexible, as I might need to change to a 5 or 7 day work week depending on the activity.

I've tried using the VBA from here but am coming up with a #NAME? error and I'm not sure this is the best tool for my needs.

Any advice is greatly appreciated!

 

[AlphaFrog]

With the NETWORKDAYS.INTL function, you can define the work week.


NETWORKDAYS.INTL function - Excel

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.
Syntax


NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) The NETWORKDAYS.INTL function syntax has the following arguments:

• Start_date and end_date Required. The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
• Weekend Optional. Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. Weekend is a weekend number or string that specifies when weekends occur.

Weekend number values indicate the following weekend days:
[TABLE="class: collapse"]
<tbody>[TR="class: trbgeven"]
[TH]Weekend number[/TH]
[TH]Weekend days[/TH]
[/TR]
[TR="class: trbgodd"]
[TD]1 or omitted[/TD]
[TD]Saturday, Sunday[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]2[/TD]
[TD]Sunday, Monday[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]3[/TD]
[TD]Monday, Tuesday[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]4[/TD]
[TD]Tuesday, Wednesday[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]5[/TD]
[TD]Wednesday, Thursday[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]6[/TD]
[TD]Thursday, Friday[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]7[/TD]
[TD]Friday, Saturday[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]11[/TD]
[TD]Sunday only[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]12[/TD]
[TD]Monday only[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]13[/TD]
[TD]Tuesday only[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]14[/TD]
[TD]Wednesday only[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]15[/TD]
[TD]Thursday only[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]16[/TD]
[TD]Friday only[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]17[/TD]
[TD]Saturday only[/TD]
[/TR]
</tbody>[/TABLE]

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.
For example, 0000011 would result in a weekend that is Saturday and Sunday.

• Holidays Optional. An optional set of one or more dates that are to be excluded from the working day calendar. holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.

Remarks

• If start_date is later than end_date, the return value will be negative, and the magnitude will be the number of whole workdays.
• If start_date is out of range for the current date base value, NETWORKDAYS.INTL returns the #NUM! error value.
• If end_date is out of range for the current date base value, NETWORKDAYS.INTL returns the #NUM! error value.
• If a weekend string is of invalid length or contains invalid characters, NETWORKDAYS.INTL returns the #VALUE! error value.

 

[MaryAnd]

Thank you AlphaFrog - unfortunately networkdays.intl is not available in 2007. This would be perfect though if anyone has a way to recreate it in 2007. I could upgrade but this is distributed to a large organization and I'm betting it isn't backwards compatible.

 

[AlphaFrog]

Sorry. I wasn't sure when it was added.

Perhaps this would help...
A Better NETWORKDAYS

 

[MaryAnd]

Thank you again but I tried that again and I'm still getting a #NAME? error. I downloaded and imported the .bas file but I've never done that before so I'll do some research to see if I'm missing something there.

 

[AlphaFrog]

Thank you again but I tried that again and I'm still getting a #NAME? error. I downloaded and imported the .bas file but I've never done that before so I'll do some research to see if I'm missing something there.

I also downloaded the .bas file and it's the wrong file for Networkdays2. The link is not correct. I'll let them know.

The .bas file is just a text file. You could copy the text directly from the web page and paste it into the VBA editor.

 

[barry houdini]

This formula will work for Mon-Thu days

=SUM(INT((WEEKDAY(MIN(E10,L10)-{2,3,4,5})+ABS(E10-L10))/7))*<l10,-1,1)
IF(E10< L10,-1,1)

That gives you the equivalent of

=NETWORKDAYS.INTL(L10,E10,"0000111")

but will work in Excel 2007 because it doesn't use NETWORKDAYS.INTL function

It doesn't exclude holidays, though. Do you need to do that?

The {2,3,4,5} part indicates which days you want to include (Sun = 1 through to Sat = 7) so you can adjust that as required</l10,-1,1)

 

[MaryAnd]

Barry that is exactly what I'm looking for - thank you so much!!