Extend Daily Dates series excluding Weekends with latest dates

[LucasLondon]

Hello,

I have a list of daily dates in column A of the format DD-MMM-YY that excludes any dates that fall on a saturday and sunday. For example the latest dates are below.

15-Jun-0916-Jun-0917-Jun-0918-Jun-0919-Jun-0922-Jun-09

What I would like to do is extend the dates to the most recent date but to exclude any weekend dates. For example if I update this file next Monday, I want to extend the above series with daily dates to 29-Jun-09, but I don't want the 27th or the 28th included in the series.

I guess the code would work something like this but there might be a more efficent way:

1) Find last populated cell in column A

Range("a65536").Select
Selection.End(xlUp).Select

2) Determine todays date
Today function (= Today)

3) Populate cells until todays date

Don't know how to do this!

4) Delete any dates that fall on a weekend from the lastest added dates.

if =WEEKDAY(cell) = 1 or 7 delete, row Don't know how to do rest of this!

The above approach is what seems most logical to me but there might be a more efficient or faster way.

Thanks,

Lucas

[dalea]

Make sure "Analysis Tool Pack" is active by goint to "Tools", "Add-ins" and making sure "Analysis Tool Pack" is checked.

Analysis tool pack has a "workday" function. If the location of the beginnig date of your series was in A6 the formula to put in A7 would be "=workday(A6,1)". Then just drag or copy that formula as far as needed.

[LucasLondon]

Hi Dalea,

Thanks for the suggestion, I've now added the formula in! Is there a way I can get the column to autofill the formula to the current date only?

The reason is because i have macros I run in this sheet (which relies on the date feild to be poulated to the most recent date to work fully) but I keep forgeting to update the dates and end up having to re-run!

So am just wondering if there something I can add to the existing macro so that it autofill automatically!

Thanks,

Lucas

[Aussiebear]

You could set it up to run as the workbook opens

[LucasLondon]

Hi,

Yes that's what I was thinking, but I was hoping someone could help me determine what the code should be based on the workday function, i.e how to I autofill Column A with the formula to the lastest date?

Thanks,

Lucas

[Aussiebear]

Post a workbook and we'l see what happens.

[Aussiebear]

In the meantime here's a non vba workaround.

[LucasLondon]

Hi Aussiebear,

This non-VBA solution should be fine for me (assuming that formula driven dates still work in lookup functions).

Thanks,

Lucas