Thread: Date Calculation Discussions (1.00) (Office 2003)

1. Date Calculation Discussions (1.00) (Office 2003)

I'm looking for the way to calculate a date for Labor Day of a future year, where the weekday, week, and month are fixed, but the day and year vary. [Ultimately, I want to be able to calculate dates for a series of meetings scheduled for the first Monday of every month, but the principles would be the same.]

{QUOTE
{SET Weekday 0}
{SET Week 1}
{SET Delay 1}
{SET yy{={DATE @yyyy}+Delay}}
{SET mm{=09}} -- would this be the correct form to use? and is there a tighter way to specify September?
{SET dd -- I think this is the hiccup!
{dd+mm+yy # "00'-'00'-'0000"} @ "dddd, d MMMM yyyy"}

Puzzled,

axsc

2. Re: Date Calculation Discussions (1.00) (Office 2003)

Welcome to Woody's Lounge!

Macropod will no doubt be able to help you with your problem in Word, but wouldn't Excel be more suitable for this kind of calculation? VBA Procedures For Dates And Times contains a custom function
<code>
Public Function NthDayOfWeek(Y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
NthDayOfWeek = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
End Function
</code>
This can be used in a cell formula in Excel to calculate the first Monday of a given month, for example in September of 2006:
<code>
=NthDayOfWeek(2006,9,1,2)
</code>
2006 = year
9 = month
1 = first week
2 = Monday (Sun = 1, Mon = 2, ..., Sat = 7)

3. Re: Date Calculation Discussions (1.00) (Office 2003)

<P ID="edit" class=small>(Edited by macropod on 19-Jul-06 21:15. Week & Weekday change added & Month offsets corrected)</P>Hi axsc,

If you copy the field from the section titled 'Calculate the day & date of a given day of the week next month' in <post#=249902>post 249902</post#>, that and the modifications outlined below will provide you with the basis for a set of field-based calculations.

For Labor Day, insert the following fields, just after the 'QUOTE' statement:
. {ASK Year "For what year do you want Labor Day?}
. {SET Month 8}
and change:
. all instances of '{Date @ 'M'}' to 'Month'
. '{DATE @ yyyy}' to 'Year'
. '{SET Week 5}' to '{SET Week 1}'
. '{SET Weekday 3}' to '{SET Weekday 0}'

For your January meeting schedule, use the same modifications, but change the prompt to 'For what year do you want the meeting schedule?' and the 'Month' value to 1. For February through December, use a copy of this field modified by deleting the line:
. '{ASK Year "For what year do you want the meeting schedule?}'
and changing the line
. '{SET Month 0}'
to
. '{SET Month 1}', '{SET Month 2}', '{SET Month 3}', ... '{SET Month 11}' for each month, as applicable.

4. Re: Date Calculation Discussions (1.00) (Office 2003)

Thank you, Hans, for the suggestions.

If this were for me alone, using Excel or alternatively a Word macro would be excellent choices. The advantage to coaxing Word field codes to do this is that, in sharing a document, co-workers won't have to find an Excel file or use a different template -- their efforts will be minimal. Given some co-workers, this is a Major advantage.

Still working on tweaking, but so far I'm getting the last/5th week of the month even though Week is Set to 1. Trying to work out where in the code this is happening ....

axsc

Hi axsc,

6. Re: Date Calculation Discussions (1.00) (Office 2003)

There's still a problem with the coding, I'm afraid. I downloaded Version 2_46, went to page 7, and updated the field for "Calculate the day & date of a given day of the week next month. " Instead of a date in August, it produced "Wednesday, 8 March 2006" . It's coded for Weekday 3, Week 1, so March 8 isn't anywhere in the ballpark.

So I laid out a column of these fields and modified them, first listing Weeks 1 through 5 and then Weekdays 0 through 6. THOSE results were so interesting I went on to add Weekdays 7 through 12. I'm still not sure why the Weeks 1, 2, and 5 aren't working -- Weeks 3 and 4 are -- but it looks as if at some point the calculations are certainly confusing Weekday with Month.

Original

7. Re: Date Calculation Discussions (1.00) (Office 2003)

Hi axsc,

Did you adjust the output to reflect your regional settings, as per the introductory notes in the file?

8. Re: Date Calculation Discussions (1.00) (Office 2003)

Bingo! Thank you!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•