Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Date calculation discussions ctd. (2.46 and later)

    Since the "Date calculation discussions" thread starting at <post:=432,773>post 432,773</post:> has become very long, it has been locked. Please feel free to post your questions about Macropod's Date Calculations in Word (2.46) in this thread.

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

    Re: Date calculation (2003)

    I have been enjoying trying to encourage macropod's inspired code to calculate my meeting dates, and for the most part I now seem to have it doing so. What I wanted was to be able to create a file with a set of 2006-2007 meeting dates -- the first Saturday of the month -- so that NEXT summer, when I need to update and reprint the file, the dates will automatically update for 2007-2008. And so on, and so on, into the distant future.

    I did end up rearranging some lines, so the first few now read in this form:
    {SET Month #}
    {SET Week #}
    {SET Weekday #}
    {SET Year {={DATE @ yyyy}+#}}
    {SET b{=(Year}+4800-a}}
    ... rest of calculation, format, etc.

    I like this because it's then easy to check the date information right at the start, and I can specify the year I need the date for.

    Most months are duly producing the first Saturday just as desired. HOWEVER, for some reason I can't work out, the December formula provides first Saturday in December for the previous year. I don't see any obvious reason why it should. File attached with a table of This Year, Next Year, Two Years from now, to illustrate the difficulty.

    Weird query: I managed to tweak the code to identify Kentucky Derby Saturday (first Saturday in May) and Preakness Saturday (third Saturday in May). However, the Belmont is run 21 days after the Preakness, a Saturday which falls between June 2 and June 8 -- as you see, it is usually, but not always, the first Saturday in June. My efforts to add 21 days to the third Saturday in May caused a problem -- no doubt with the Limit code line -- and the date currently reads 40 May 2006. This is probably correct, so to speak, but a date given as "June" something would be preferable! Would it be preferable approach to attempt to add 21 days to the third Saturday in May, or would it be easier to specify "Saturday" with limits set to >June 1 and <June 9??

    Many thanks,

    axsc
    (no hurry -- leaving town for a week in the morning)
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date calculation (2003)

    Macropod is on vacation at the moment, so you'll have to wait some weeks before he'll be able to reply.

    His brilliant field codes are excellent for occasional date calculations, but Word is not the most appropriate application for large numbers of calculations, It would be much easier to accomplish what you want in Excel, since it is geared towards working with calculations and formulas, and since it has a large number of date functions built in.

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

    Re: Date calculation (2003)

    It's an excellent suggestion, but I'm using these calculations in documents for people who can cope -- more or less -- with Word, but seldom with links between Excel and Word. So the Word calculations are most helpful. In any case, I have now solved the horse race problem -- added a Delay of 3 weeks to the Preakness date, which seems to work well and was less complicated than 21 days or a range of dates. Have also worked out a calculation for Mardi Gras which also seems to work well.

    Am still wondering why a December calculation will produce a date a year off, though.

    Hope Macropod has had a splendid vacation and returns from it -- eventually -- very fit and raring to go.

    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 (2003)

    Hi asxc,

    Attached is a revised copy of your meeting schedules document.

    To calculate the Belmont event date as 21 days offset from the Preakness date, I first changed the 'dd', 'mm' and 'yy' bookmark names in the Belmont field to 'pdd', 'pmm' and 'pyy', respectively. Then I modified the formula found under 'Calculate a day, date, month and year, using n (301) days delay' in my tutorial document to refer to those bookmarks instead of the system date and changed the 'Delay' value from 301 to 21.

    As for the December anomaly, I fixed it by changing the expression 'MOD(Month,12)' to 'MOD(Month-1,12)+1' throughout. Thanks for finding this. Looks like I'll have to update the tutorial!
    Attached Files Attached Files
    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 (2003)

    Hi, macropod,

    Many thanks for the calculations!

    axsc

  7. #7
    New Lounger
    Join Date
    Dec 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculations (2003)

    I was wondering if you can help me write code to subract one from a date. The data is imported from an excel spreadsheet. My attempt at changing your code was futile. Any help would be appreciated.

    Thanks.

  8. #8
    New Lounger
    Join Date
    Dec 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculations (2003)

    The problem I am having now, is that I have white space where the code is supposed to be. Why is this?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Calculations (2003)

    Welcome to Woody's Lounge!

    Could you post a copy of the document with the date and with your attempt at the calculated field? You can remove everything else from the copy.

  10. #10
    New Lounger
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation discussions ctd. (2.46 and later)

    Hi, I just read macropod's document and it is Fantastic (THANKS!) but I had a slight problem.

    At the "Automatically Insert a Past or Future Date" section, any that calculate a date using n months delay seem to trip at the change of a year. For example, it is now January. If I want to set the delay to -1 (go back one month) it still returns January. Some of the others trip up completely. I didn't see this problem addressed yet in the previous threads, if I have missed it, can someone please point me to the discussion?

    Thanks

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date calculation discussions ctd. (2.46 and later)

    Welcome to Woody's Lounge!

    Apparently, the formulas don't handle negative delays correctly.
    Select the entire field and press Shift+F9 to display field codes.
    Part of the field code will look like

    {=MOD({DATE @ M}+Delay-1,12)+1}

    Change the -1 after the word Delay to +1199

    {=MOD({DATE @ M}+Delay+1199,12)+1}

    This effectively adds 1200 months to the delay; this is sufficient to handle negative delays up to -1200 months.
    Press F9 to hide the field codes and update the field.

  12. #12
    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 ctd. (2.46 and later)

    Hi tktm,
    I'll be posting an update ASAP. In the meantime, you can use:
    {SET mm{=MOD(ABS({DATE @ M}+Delay+11),12)+1}}
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    New Lounger
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation discussions ctd. (2.46 and later)

    Thanks macropod and HansV! Those both worked like a charm on rolling the month back, but the corresponding year still isn't coming out right.

    update: I've narrowed the problem down to the fact that while Excel says the INT(-.08333) is -1, Word persists in telling me it is 0. Anybody know a workaround for Word's abuse of mathematical conventions?

  14. #14
    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 ctd. (2.46 and later)

    Hi tktm,

    That too has been fixed, via:
    {SET yy{=INT({DATE @ yyyy}+(Delay+{DATE @ M}-1)/12)}}

    You can now download the update Date Calc 'tutorial' in <post#=249902>post 249902</post#>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  15. #15
    New Lounger
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation discussions ctd. (2.46 and later)

    Awesome! Thanks so much <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Page 1 of 3 123 LastLast

Posting Permissions

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