Difference in Dates

[atulrajratna]

Hi team,


I need upcoming action by dates which we use to update the system. we don't calculate Saturday & Sunday as they are non business days.


e.g-

A B C

2| Enter Date here- | 17-11-2011 | Thu


so i get-


A B C

4| 1 business date | 18-11-2011 | Fri

5| 2 business date | 21-11-2011 | Mon


we need 1 to 10 business days excluding sat-sun. I made this sheet using if formula and it works, but there are some dates which we don't calculate (like sat-sun) B'coz they are holidays announced by client.


Now suppose we have client holiday on 21-11-2011 so i should get dates like..

A B C

2| Enter Date here- | 17-11-2011 | Thu

3|

4| 1 business date | 18-11-2011 | Fri

5| 2 business date | 22-11-2011 | Tue


Please help me in this.

Thank you!

 

[GCExcel]

Hi Atul,


You can use the formula NETWORKDAYS. For example: A4=NETWORKDAYS($B$2,B4-1,$E$3:$E$6)


where you specify the start date (B2)

the end date (B4)

and a range containing the holidays (E3:E6)


Note: I added -1 to the end date because both start date and end date counts.

 

[atulrajratna]

Hello sir,

Thank you so much for the reply.

I tried by NETWORKDAYS formula, but its not working..

 

[Hui]

Atul

Are the Dates in Column B, Dates or Text ?

Click the , icon on the menu, if the dates change to 40864 etc they are dates

if they don't change they are text.


they need to be entered as dates eg: 17/11/2011 or in the US 11/17/2011

 

[oldchippy]

Hi Atul,


When you say it's not working are you using xl2003?


http://office.microsoft.com/en-us/excel-help/networkdays-HP005209190.aspx

 

[atulrajratna]

Hi all,

I am using XL 2003.

and

in column B, They are not text, they are dates only (i.e 40864 - 17/11/2011).


I am working on 17/11, and i need to update the system for next business day. if i am using +1, it will become 40865 i.e 18/11/2011 as 1 business day, but if we have official holiday on 18/11/2011, I should have get 18/11/2011 as 1 business day.

(consider there is a list of 10 official holidays in column D)


Please help me in this.

Thank you!