+ Reply to Thread
Results 1 to 2 of 2

Add value based on year

  1. #1
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Add value based on year

    Hi,

    I know this is really simple but I'm having a bit of a brain block right now.
    =IF(C2="","",MONTH(C2)+5)

    Is my basic formula, where Column C is full of dates in DD/MM/YYYY format

    The problem of course is that it cannot handle year breaks, so January will revert the result back to 1 (+5)

    I want it to add an additional 12 for every year beginning 2011 without having to have to do it manually along the lines of:
    =IF(C2="","",MONTH(C2)+5+12) for 2011
    =IF(C2="","",MONTH(C2)+5+12+12) for 2012

    etc

    I was thinking of using MATCH or CHOOSE.

    [edit]
    Came up with this, but seems overly complicated:
    =IF(EP2="",IF(C2="","",MONTH(C2)+5+12*(YEAR(C2)-2010)),EP2)
    Last edited by dip11; 09-06-2012 at 07:14 AM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Add value based on year

    maybe
    =IF(C2="","",DATEDIF(DATE(2011,1,1),C2,"m")+1)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1