Day count problem

[Drewgarry007]

Hi,

I am trying to use the below loop to populate a date value, but it hasnt worked properly today becuse is a holiday on friday

Range("A2").Select
Do Until ActiveCell.Offset(0, 1) = ""
If ActiveCell.Value = "" Then
ActiveCell.Offset(0, 3) = "=WORKDAY(TODAY(),1)"
End If
ActiveCell.Offset(1, 0).Select
Loop

Is there a way where I can account for holidays such as the one tomorrow? the above returns a value of 11Nov11 when it should return 14Nov11 ??
Is there some way using Networkdays maybe??

Thanks

 

[AlphaFrog]

The WORKDAY function supports holiday exclusion just like NETWORKDAYS.

Syntax

WORKDAY(start_date, days, holidays)

Start_date is a date that represents the start date.

Days is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

Holidays is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

 

[Drewgarry007]

So how do I get my formula to return 14Nov11?

 

[AlphaFrog]

Range("A2").Select
Do Until ActiveCell.Offset(0, 1) = ""
If ActiveCell.Value = "" Then
ActiveCell.Offset(0, 3) = "=WORKDAY(TODAY(),1, [COLOR="Red"]A4:A10[/COLOR])"
End If
ActiveCell.Offset(1, 0).Select
Loop

Change A4:A10 to the range of cells where you have a list of all holiday dates.

 

[Drewgarry007]

hmmm if I dont have a list of holiday days. Is there not a list of holidays somewhere in an excell I can use? or is there a function that can be used?
I know the NETWORKDAYS function takes into concideration holidays...

 

[AlphaFrog]

You have to make your own Holidays list. Everybody has a unique list for their holidays e.g.; not everyone has Veterans' Day as a holiday. Networkdays works the same way.