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

    Date Calculation Election Schedule

    My efforts to tweak the field codes to calculate a date range hasn't been successful.

    What's needed is an automatic update for the beginning and end of an organization's three-year election cycle. The formula I need to begin with is
    for the start year:

    {DATE (yyyy) - MOD({DATE yyyy}, 3), \@ "yyyy"}

    that is, the current year less the MOD for the current year divided by 3, so that for 2013, 2014, and 2015 the result would be "2013."

    The end of the election cycle would be that number plus three - "2016."
    In 2016, the calculation would update so the two numbers would be "2016" and "2019."

    It doesn't matter whether the result is a date or a 4-digit number.

    I think I'm probably not extracting the year as a number to run this formula on. Any suggestions would be most welcome!

    Sincerely,

    axsc

  2. 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
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Madison, Wisconsin, Wisconsin, USA
    Posts
    1,694
    Thanks
    55
    Thanked 63 Times in 61 Posts
    Charles Kyle Kenyon
    Madison, Wisconsin

  4. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes, indeed -- I've been a devotee of several versions of Date Calculation -- I have at least three on the desktop right now. I have a field code that adds a year, or subtracts a year, from the current year, but that format isn't helping with this particular approach. My best guess at this moment is that Word is contemplating "2014" as a date and therefore not letting me use it in a MOD argument as a number.

    Thank you,

    axsc

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    I am not sure that I really understand what you are trying to achieve but this appears to be what you are looking for

    {={Date \@ "yyyy"} - Mod({Date \@ "yyyy"},3)}
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. The Following 2 Users Say Thank You to Andrew Lockton For This Useful Post:

    axsc (2014-05-16),Charles Kenyon (2014-05-16)

  7. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Perfect! Your code produced the original number, and I added a "+3" at the end for the end date of 2016. This is one of those documents that goes on for decades, and having a field code automatically updating as necessary will be one more proofreading trap avoided.

    Many, many thanks,

    axsc

Tags for this Thread

Posting Permissions

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