Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculating a new date

    I have a spreadsheet that tracks employee attendance. One of the cells at the top of the worksheet is for the employees hire date, in the form mm/dd/yyyy. In another cell in the worksheet, I need to list the employees vacation reload date, which is always the first day of the month following their anniversary date. So if an employees anniversary date is 8/24/1997, their reload date would be 9/1/2001. I am looking for help on how to calculate this automatically, without having to type it in each year.

  2. #2
    calacuccia
    Guest

    Re: calculating a new date

    Hello Steve

    with the anniversary date in cell A1, put this formula where you want to calculate the Reload Date

    =DATE(YEAR(NOW()),MONTH(A1)+1,1)

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating a new date

    Steve, hi.

    I've done this type of thing in the past, and I've used the EOMONTH function, which calculates the last day of the month a number of months from a given date. For example, =EOMONTH("1 Feb 2001",1) returns 31st March 2001, which is the end of the month that contains 1 MAR 2001, one month away from 1 Feb 2001.

    The offset number can be negative or zero as well as positive, and I use this to find the last day of the current month and add one to it (to give the day after). So, if A1 contained your date (8/24/1997 in your example), then:

    =EOMONTH(A1,0)+1

    would give you 9/1/1997.

    The remaining part of the process is to change the year to be the current year. The attached example shows how I've done it.

    There may be better ways, but this has always worked for me. Note, by the way, that you need to install the Analysis Toolpak add-in to get the EOMONTH function.

    Regards,

    Stuart
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating a new date

    Thanks for the quick response. Both methods worked excellent.

Posting Permissions

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