+ Reply to Thread
Results 1 to 5 of 5

Change year but keep the month the same based on current year

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Change year but keep the month the same based on current year

    I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair. the problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015. Attached is an example ** what I'm working on.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Change year but keep the month the same based on current year

    See if this solution will do for you.

    Just add the values jan'15 and feb.15 in the sheet (I marked them blue).

    Then you have to expand your formula (I marked them yellow).

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    10-16-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Change year but keep the month the same based on current year

    Thank you for the idea, and if I were only going to use this budget for a year or less that would work but it would draw the table out to much for what I intend, I'm working on closer to a 10 year plan so that would mean using 120 columns for the data and I would rather not do that. I think I may have it figured out now though. I nested year and today functions into the date function and it gives me the desired results my formula looks like this
    Please Login or Register  to view this content.
    and then I'll just change the dates for the 12 months. again thank you for taking time out ** your day to help me out.

    interesting for some reason the forum doesn't like me saying o f = **
    Last edited by Jamon Fries; 03-08-2014 at 06:05 PM.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Change year but keep the month the same based on current year

    Please Login or Register  to view this content.
    If the budget is differant in all 10 years your solution won't work.

    My solution provides a dynamic solution.

    Great you find a solution yourself.

    The o f problem is a already know problem for the technic team of this forum.

  5. #5
    Registered User
    Join Date
    10-16-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Change year but keep the month the same based on current year

    yeah I'm aware that my solution limits my budgeting capabilities somewhat, but as situations arise and I need to alter the budget for future years it will be on the current month and I can just change it then, I have no need to keep the older months for my purpose so any changes will be needed to be made in current months, so I'm not really worried about that limitation.

+ 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. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  2. Replies: 1
    Last Post: 08-13-2012, 11:18 AM
  3. Current Year & Month Name, Previous Year & Month Name
    By mithesh in forum Excel General
    Replies: 9
    Last Post: 10-21-2011, 07:00 AM
  4. Macro to change a row of dates in line with current month and year
    By widodude in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-02-2011, 06:50 AM
  5. Change the cell focus based on month and year
    By gruf1968 in forum Excel General
    Replies: 4
    Last Post: 01-24-2010, 12:30 PM

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