Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lease expiry dates (2003 sp2)

    Good morning

    If I have a date in say cell A1 (lease start date) and a number in B1 (Lease months) how can I multiply A1 and B1 to return a date in C1 that is, for the purposes of my requirment, 33 months on from the date in cell A1

    Thank you

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lease expiry dates (2003 sp2)

    How about this:
    =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lease expiry dates (2003 sp2)

    Thanks Steve, that is great

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lease expiry dates (2003 sp2)

    Steve

    I would just offer a word of caution on this - the formula works fine so long as the day of the lease start date is not near the end of the month. If it is on say the 30th or 31st of the month you may get unexpected results.

    For example using the formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) where A1 is Jan 30th, and B1 is 1 gives the result of March 2nd. This may not be what you wanted.

    I'm not sure of the best way to get round this if you need to.

    Nick

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

    Re: Lease expiry dates (2003 sp2)

    If you activate the Analysis ToolPak add-in, you can use the EDATE function:

    =EDATE(A1,B1)

    If A1 = January 30, 2007 and B1 = 1, the result of the formula will be February 28, 2007.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lease expiry dates (2003 sp2)

    Hans

    That is exactly what I was looking for to use in another worksheet. Your knowledge is, as usual, spot on.

    Thanks

    Nick

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lease expiry dates (2003 sp2)

    Yes this will never happen if the day of the month is <29.

    It will happen when the day of the current month has more days than the future month. If the future month has less days then the future date will be 1-3 days later than the last day of the future month (depending on how many days difference)

    If that is a concern and if the date should never be beyond the last day of the "B1"th month then you could use:
    =MIN(DATE(YEAR(A1),MONTH(A1)+B1+1,0),DATE(YEAR(A1) ,MONTH(A1)+B1,DAY(A1)))

    This formula will yield (for example) if B1 is 1 month, from Jan 1 - 28 will yield Feb 1-28 (respectively) and Jan 29-31 will all yield Feb 28.

    Steve

Posting Permissions

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