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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,772
    Thanks
    0
    Thanked 162 Times in 150 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]

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,772
    Thanks
    0
    Thanked 162 Times in 150 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
  •