Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I downloaded the newest DateCalc.zip file, DateCalc V2_85.doc from http://bro.ws/249902L, and happily buried myself in it, but found that when I arrived at "Calculate the day & date of the nth day of next month", it gives me a date in January, but January 2009; it won't roll over to next year. Anything I tried to tweak the formula produced garbage, so I don't understand all the ramifications as well as I'd like to.

    Since I'm also looking for a good way to calculate the last day of last month and the last day of next month, I wonder whether there's a way to communicate "this month plus two months, day 0" to get the last day of next month.

    Many thanks,

    Ann

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,919
    Thanks
    0
    Thanked 194 Times in 177 Posts
    Hi Ann,

    Try changing the line:
    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}-1)/12)}}
    to
    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}>mm))}}

    As for your second problem, the tutorial has an example titled "Calculate the day & date of the last or nth-to-last day of this month". You can adapt that example, changing it to:
    {QUOTE
    {SET Offset 0}
    {SET mm{=MOD({DATE \@ M}+Offset-1,12)+1}}
    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}>mm))}}
    {SET dd{=MAX(1,IF((mm=2),28+(MOD(yy,4)=0)+(MOD(yy,400)= 0)-(MOD(yy,100)=0),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({ DATE \@ d}>30)=1,30,31)))}}
    "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
    Note that the 'SET yy' field is the same as described above (so you can use copy/paste to replicate that part.
    As coded, the field calculates the last day of this month. Changing the Offset from 0 to 1 or -1 returns the last day of next month or last month, respectively.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,919
    Thanks
    0
    Thanked 194 Times in 177 Posts
    Hi Ann,

    A better solution for the first problem is to change the line:
    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}-1)/12)}}
    to:
    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M})/12)}}
    Note that the only change from the original construction is the deletion of '-1'.

    For the second problem, a better solution would be:
    {QUOTE
    {SET Offset 0}
    {SET mm{=MOD({DATE \@ M}+Offset-1,12)+1}}
    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}+Offset-1)/12)}}
    {SET dd{=MAX(1,IF((mm=2),28+(MOD(yy,4)=0)+(MOD(yy,400)= 0)-(MOD(yy,100)=0),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({ DATE \@ d}>30)=1,30,31)))}}
    "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
    The only change here is for the '{SET yy' field, which is now the same as for the first solution but with the addition of '+Offset-1'. Amongst other things, this handles negative months correctly when they result in a year change and allows the offset to be any number of months plus or minus.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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