# Day count problem

#### Drewgarry007

##### Board Regular
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.

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

Code:
``````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.

Last edited:
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...

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.

Replies
0
Views
109
Replies
8
Views
322
Replies
10
Views
339
Replies
1
Views
166
Replies
4
Views
314

1,196,057
Messages
6,013,176
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back