Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    Hi axsc,

    You'll need to download the latest version fo the Date Calc document. The earler version has a bug in th field coding.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •