monthly cycle events to add to calendar object

[Guest]

I have what seems to be quite the doozie of a task... I am trying to create a
calendar for my work group which has events that occur on a monthly cycle. I
want to use a calendar object where I select the month and year, and excel
automatically input the events into their correct days.

The events are organized according to what working day of the month they
fall on. For example, event 1 occurs on the first working day of every
month. Event 7 would fall on the 5th to last working day of every month.

How would I accomplish this? Would I run into any issues if there are
multiple events on the same day?

Thanks in advance!

 

[Tyro]

When you say 5th to last workday, you mean the event will occur every
workday starting with the 5th workday through the last workday of the month?
Do you want to see an event then a date range (starting workday through
ending workday) for the event?
You'll have to provide rules for each event as to when the event occurs.
Example: can an event occur on the 2nd workday and on the 15th? If so, a
date range won't work. You'll need to see every date the event occurs.
As for issues with multiple events occurring on the same workday, Excel
couldn't care less. You might.
What I'm trying to say is that you'll have to be more specific before we can
answer you.

 

[Guest]

Each event only occurs once a month (one date, not a range), but can be on a
different day each month. We assign the day each month as D for the 1st
working day of the month, D+1 for the second, etc. When dealing with so many
days before month end, D-1 would be the last day of the month, D-2 the second
to last, and so forth.

I mainly want to open the calendar, select (for example) March 2008, and see
that I have to do item X on D+5 which is displayed as item X on Friday, March
7. Other events that month may be item Y on D-3, displayed as item Y on
Thursday, March 27th.

I would also want it be graphical, not displayed as a list.

 

[Tyro]

Well, it's confusing if you refer to D as both the first and last work
date - 1 in the month. Can you give me examples of actual dates? I mean D
cannot be both March 3, 2008 and March 31, 2008. If your workweek runs from
Monday through Friday and D is March 3, then D+5 is March 10. If D is March
31, then D-3 is March 26. These computations ignore weekends. Does your
workweek run from Monday through Friday? The maximum number of workdays you
can have in a month is 23, assuming 2 non workdays per week. Are you going
to have the event #1 in A1 and the workday of month in B1 with the actual
date in C1, event #2 in A2 and the workday of month in B2 with the actual
date in B3, etc?

 

[Tyro]

Correction B2 not B3

Well, it's confusing if you refer to D as both the first and last work
date - 1 in the month. Can you give me examples of actual dates? I mean D
cannot be both March 3, 2008 and March 31, 2008. If your workweek runs
from Monday through Friday and D is March 3, then D+5 is March 10. If D is
March 31, then D-3 is March 26. These computations ignore weekends. Does
your workweek run from Monday through Friday? The maximum number of
workdays you can have in a month is 23, assuming 2 non workdays per week.
Are you going to have the event #1 in A1 and the workday of month in B1
with the actual date in C1, event #2 in A2 and the workday of month in B2
with the actual date in B3, etc?

 

[Tyro]

I'm tired. I'll get it right yet. Not B3, C2.

Correction B2 not B3

 

[Guest]

Yes- I want to ignore weekends. In counting, I only count workdays (holidays
are counted as non workdays as well).

D is always the first day of the month. When referencing the last day of
the month, you term D-1, where as you are one day before the first day of the
next month. Think of it as a number line where D is your middle point (or 0
as you originally learn). From starting point D, you can count forward into
the current month (positive) D+1, D+2, etc; or you can count backward to the
previous month (negative) D-1, D-2, etc.

I've never done any programming in VBA, but I have some java experience. I
know that Cell(x,y) = Calendar1.Value will assign values to a cell, but
beyond that I have a huge gap to fill. I was thinking it will be easy to
count workdays as there is a workday function in excel, but going backward in
the month will be more difficult.

Is there a similar function like .length in java where I can find the value
of the last item, and then count backward?

 

[Tyro]

Well, going forwards or backwards makes no difference. You need to explain
with examples what your starting date D is what cell it's in, how you plan
to indicate which workday of the month an event occurs and what cell that is
in and where you want the result date put..