+ Reply to Thread
Results 1 to 9 of 9

increasing the year in a column

  1. #1
    Registered User
    Join Date
    10-20-2020
    Location
    Brandon, Suffolk, UK
    MS-Off Ver
    mac user. numbers 5.1
    Posts
    6

    increasing the year in a column

    bit of a novice and also on a mac, i hope the xlsx is good.

    this is the local legion membership list, columns J and K show branch and club joining date ( should be same but can differ )

    I want to upload data to epos to give current members 10% off beer ( good hey )

    How do i alter "J" from 11/02/1994 to 11/02/2020

    then column "K" to be expiry date 12 months on 11/02/2021
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: increasing the year in a column

    Your words and your sheet do not tally. If you want column K to be 1 year on from column J,

    =EDATE(J6,12)

    If not, then you need to be more specific about what you want and MAKE SURE that your text and your words tally. If someone joined 10 years ago, how will Excel know if they are still current? Shouldn't there be a column for "current member" or is veryone on the list assumed to be a current member?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-20-2020
    Location
    Brandon, Suffolk, UK
    MS-Off Ver
    mac user. numbers 5.1
    Posts
    6

    Re: increasing the year in a column

    Glen the info you gave me was spot on but again my poor explanation didnt help. I struggle to explain when im unsure so can i give you an example

    not important but
    J current reads * *would like that to become * * then K becomes
    * 01/01 1991 * * * * * * * *01/01/2020 * * * * * * * * * 01/01/2021 *

    the value in "J" does change for each member

    the * *=EDATE(J6,12) * *does alter the column exactly as i asked and i can see how J6 is cell and 12 is * added to the months giving a one year advance.*
    Last edited by suffolk cabby; 10-20-2020 at 09:08 AM.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: increasing the year in a column

    Please insert manually calculated results in your sheet. Your explanation is not clear at all

  5. #5
    Registered User
    Join Date
    10-20-2020
    Location
    Brandon, Suffolk, UK
    MS-Off Ver
    mac user. numbers 5.1
    Posts
    6

    Re: increasing the year in a column

    manually entered data.

    in row 6 the member has been involved since Feb 1998 & renewed Feb of current year so shows 2021

    in row 7 the member has been involved since Dec 1998 renewal due Dec of current year so shows 2020
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: increasing the year in a column

    Probably a shorter way to achieve this.... but it's obvious and it works...

    =IF(J6="","",IF(NOW()>DATE(YEAR(NOW()),MONTH(J6),DAY(J6)),DATE(YEAR(NOW())+1,MONTH(J6),DAY(J6)),DATE(YEAR(NOW()),MONTH(J6),DAY(J6))))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-20-2020
    Location
    Brandon, Suffolk, UK
    MS-Off Ver
    mac user. numbers 5.1
    Posts
    6

    Re: increasing the year in a column

    Glenn, spot on absolute star, apologies for poor descriptions to you guys it's hard when you dont know what youre talking about.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: increasing the year in a column

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: increasing the year in a column

    Alternative solution in K6:

    Please Login or Register  to view this content.
    it compares (today of next year) with (same day same month of J6) of this year and next year, then returns the smaller one.

    OR

    Please Login or Register  to view this content.
    It counts months from J6 till today, adds up to nearest 12 month.
    Last edited by bebo021999; 10-21-2020 at 12:40 AM.
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compound interest formula with Increasing interest each year HELP!
    By eugchen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-19-2020, 05:02 AM
  2. [SOLVED] Order increasing month / year
    By Berna11 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2019, 04:06 PM
  3. =DATE(YEAR, MONTH, DAY) stop month increasing.
    By oneblondebrow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2016, 09:32 AM
  4. [SOLVED] Sine wave with increasing peaks and increasing troughs
    By JimDandy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-18-2013, 06:28 PM
  5. [SOLVED] Numeration increasing by year
    By pgft in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-03-2013, 04:31 AM
  6. What regular savings, increasing every year to reach goal
    By flossy129 in forum Excel General
    Replies: 0
    Last Post: 06-13-2012, 06:38 AM

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