+ Reply to Thread
Results 1 to 13 of 13

code that will check the date from a table if its MM/YY is current and if not then date=YY

  1. #1
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    code that will check the date from a table if its MM/YY is current and if not then date=YY

    When Year in Year/month column is current then retain Year/Month value, if year in Year/Month column is prior year then Year/Month2 = Year.

    Year/Month Year/Month2
    2014/May
    2013/april
    2014/jan

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,682

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Quote Originally Posted by richzter View Post
    When Year in Year/month column is current then retain Year/Month value
    Not sure what do actually mean by the word retain


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Quote Originally Posted by :) Sixthsense :) View Post
    Not sure what do actually mean by the word retain
    sorry.. it means that if the yy/mm is current then Year/Month=yy/mm

  4. #4
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    if the 1st column is 2014/May then 2nd column is 2014/may
    if its 2013/june then 2nd column is 2013

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,682

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Your condition part is very clear in your first post itself but wants to know the action part only...

  6. #6
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Quote Originally Posted by :) Sixthsense :) View Post
    Your condition part is very clear in your first post itself but wants to know the action part only...
    im sorry what do you mean by action part?

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,682

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Better show the expected result for Year/Month2 column for the given examples for giving exact solution

  8. #8
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Year/Month Year/Month2
    2014/May 2014/May
    2013/april 2013
    2014/jan 2014/jan

    uhmm.. if i may add.. do you know a code that will not rely on the cell name? but for the column name or ranges.. so that if the user suddenly enters another column.. the codes wont be rendered useless..

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,682

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Don't know the real need for VBA here... what about a simple formula solution?

    In B1 Cell

    =IF(LEFT(A2,4)=TRIM(YEAR(TODAY())),A2,LEFT(A2,4))

    Drag it down...

  10. #10
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Quote Originally Posted by :) Sixthsense :) View Post
    Don't know the real need for VBA here... what about a simple formula solution?

    In B1 Cell

    =IF(LEFT(A2,4)=TRIM(YEAR(TODAY())),A2,LEFT(A2,4))

    Drag it down...
    yeah thanks.. but what if the user suddenly adds a column...

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,682

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Quote Originally Posted by richzter View Post
    yeah thanks.. but what if the user suddenly adds a column...
    The target cell will not get changed and it will target the source cell always.

    If you want to set it to a new column then just change the source column name (in the formula) to your newly created column.

    Pleaes let me know if you want something else...

  12. #12
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Quote Originally Posted by :) Sixthsense :) View Post
    The target cell will not get changed and it will target the source cell always.

    If you want to set it to a new column then just change the source column name (in the formula) to your newly created column.

    Pleaes let me know if you want something else...
    uhmm.. 1 last thing.. can you explain how it works?

  13. #13
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: code that will check the date from a table if its MM/YY is current and if not then dat

    Quote Originally Posted by :) Sixthsense :) View Post
    The target cell will not get changed and it will target the source cell always.

    If you want to set it to a new column then just change the source column name (in the formula) to your newly created column.

    Pleaes let me know if you want something else...
    please explain how it works.. so i know how to use it next time.. thanks

+ 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] Need average of days for date values from date to current day that updates current date
    By FinGhost in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-12-2013, 04:41 PM
  2. Auto Collapse/Expand a Date Pivot table based on current date
    By Dave27 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-03-2013, 12:24 PM
  3. [SOLVED] VBA code for cell date is less than current date + 2months
    By craigten in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-07-2013, 09:21 AM
  4. [SOLVED] To count the number of rows based on date and check how old the row is from current date.
    By raovv in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-04-2013, 11:45 AM
  5. Replies: 4
    Last Post: 09-01-2011, 03:25 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