WORKDAY.INTL function equivalent in Excel 2007

[Supat]

Hello,

Can someone help me how to define just Sunday as weekend in the workday function in excel 2007? I have a list defined for 'HOlidays' which I'd like to the excluded in addition to Sunday for the WORKDAY function.


Thanks much in advance.

Supat

 

[bobhc]

Good day Supat

The following is from a post by Hui that I have saved you may find it of help. If it does what you want thank Hui not me..


=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Use a Weekend number of 7 = (Friday & Saturday)


A1=start_date

A2=end_date

=NETWORKDAYS.INTL(A1, A2, 7, [holidays])

Holidays are optional


Networkdays.Intl allows the user to define any day as a "weekend"


It has 14 built in combinations of weekends

eg: Weekend number values indicate the following weekend days:


Weekend number Weekend days

1 or omitted Saturday, Sunday

2 Sunday, Monday

3 Monday, Tuesday

4 Tuesday, Wednesday

5 Wednesday, Thursday

6 Thursday, Friday

7 Friday, Saturday

11 Sunday only

12 Monday only

13 Tuesday only

14 Wednesday only

15 Thursday only

16 Friday only

17 Saturday only


As well as allowing a string to define custom weekends

eg: 1010100 Will define Monday, Wednesday and Friday as weekends

eg: 0101010 Will define Tuesday, Thursday and Saturday as weekends

 

[oldchippy]

Hi Supat,


This link might help?


http://www.cpearson.com/excel/BetterNetWorkDays.aspx

 

[Supat]

bobhc -thanks for your response. The networkdays.intl works only in excel 2010, and not in 2007. I am on excel 2007 and that's why I am looking for an alternative.


oldchippy - thanks for your response. I went over pearson's link before posting here - the link has a VB code which I do not know how to import it to excel 2007. But the result is exactly what I am looking for. Can you please guide me?


Thanks,

Supat

 

[Luke M]

Supat,


To install the function into VB, instructions are as follows:


1. Right click on sheet tab, view code

2. Goto Insert - Module

3. Copy VB code that you want, paste into the newly created module.

4. Close the Visual Basic Editor (VBE)

5. Workbook is now ready to use the User defined function (UDF)

 

[Supat]

Thank you very much Luke M! Highly appreciate your help!

 

[Luke M]

You're welcome, hope the formula works out well for you.

 

[Supat]

Hello,

Is there an excel function that can give me due dates excluding holidays and weekends (sat and sunday), but including the first saturday after each quarter end? Here's an example:

Task Due day Due date

A 2 7/3/2012

B 3 7/5/2012 excluding July 4th weekend

C 4 7/6/2012

D 5 7/7/2012 including 1st Saturday in the quarter.

E 6 7/9/2012

F 7 7/10/2012

G 8 7/11/2012

H 9 7/12/2012

I 10 7/13/2012

J 11 7/16/2012 excluding Sat and Sunday

K 12 7/17/2012

L 13 7/18/2012


Thanks so much,

Supat

 

[Supat]

I am using excel 2007

 

[Supat]

Here's an example of the worksheet:


https://docs.google.com/spreadsheet/ccc?key=0AsoAJIfjMjo2dFdRUHhNdGg4OC1INHAwSzVmTU5Xa2c


Thank you!

Supat

 

[Supat]

Hello,

Can anyone help me on this please?

Thanks!

 

[NARAYANK991]

Hi Supat ,


If you can wait over the weekend , I can let you have the solution by Monday morning.


Narayan

 

[Supat]

thanks Narayan, I'll wait.

 

[NARAYANK991]

Hi Supat ,


Can you check out the workbook at the following link ?


https://docs.google.com/open?id=0B0KMpuzr3MTVMlN3eFNfRUVhSDA


Please download the file and open it in Excel , to check it out.


I have modified only one statement , since the other changes required have been implemented through formulae on the worksheet labelled Sheet1.


On Sheet2 , I have put in both Pearson's UDF and Excel's native NETWORKDAYS formula ; try out both by putting in various start and end dates in B1 and B2. In the first quarter , the two should differ by 1 after the first Saturday , in the second quarter , if the start date is not changed , the two should differ by 2 , and so on.


If you find any mistakes , let me know.


Narayan

 

[Supat]

Narayan, I truly appreciate your response. Unfortunately when I click on the link, I don't see anything. It says no preview available.

 

[Supat]

Nevermind, I was able to download. Will look at it and get back soon. Thanks again

 

[Zakir]

I am using below formula to get exact number of days excluding Sunday but its not working (counting sundays also). can any body help me to deal with this.

=WORKDAY.INTL($I$39,N38,11,Holidays!$A$2:$A$46)-1

 

[NARAYANK991]

I am using below formula to get exact number of days excluding Sunday but its not working (counting sundays also). can any body help me to deal with this.

=WORKDAY.INTL($I$39,N38,11,Holidays!$A$2:$A$46)-1

Hi ,

It seems to be working correctly ; can you post an example of where it does not work ?

Narayan