+ Reply to Thread
Results 1 to 12 of 12

If date older than 2 years, insert Reuse in formula cell

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    17

    If date older than 2 years, insert Reuse in formula cell

    Hello, everyone
    In my spreadsheet, column H contains the termination date for an ex-employee.
    Column I needs to have "Reuse" when the date in column H is 2 years old. Following is the formula I tried using. I receive error message "You've entered too many arguments for this function".

    =IF(H13>0,(And(DATE(YEAR(H13)+2,MONTH(H13),DAY(H13),"REUSE""")))

    If I remove one of the quotes at the end, I receive error message "The formula you typed contains an error".
    =IF(H13>0,(And(DATE(YEAR(H13)+2,MONTH(H13),DAY(H13),"REUSE"")))

    Thank you in advance for your help.

    Kind Regards,
    Linda

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: If date older than 2 years, insert Reuse in formula cell

    =IF(AND( H13>0, DATE(YEAR(H13)+2,MONTH(H13),DAY(H13) ) ) ,"REUSE","")

    What are you using H13 for ?
    to avoid a blank cell ?
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If date older than 2 years, insert Reuse in formula cell

    H13 is the cell that contains the termination date. Once the termination date is two years old, we can reuse data associated with that employee (i.e inspector's stamp).
    The formula will apply to all cells in column H; H13 was the first cell that had a termination date in it.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: If date older than 2 years, insert Reuse in formula cell

    in which case you need to put into the first row of data and copy down (using that row in the formula)
    Or put the formula above in your 13 and copy up and down

  5. #5
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If date older than 2 years, insert Reuse in formula cell

    It returns the same error code whether the formula is in cell I1 or I31.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: If date older than 2 years, insert Reuse in formula cell

    what error code ?
    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  7. #7
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If date older than 2 years, insert Reuse in formula cell

    Hi, Etaf
    I listed the error code in my first entry.
    In my spreadsheet, column H contains the termination dates for ex-employees.
    Column I needs to have "Reuse" entered into it when the date in column H is 2 years old. Following is the formula I tried using in Column I.

    I receive error message "You've entered too many arguments for this function".
    Code is:
    =IF(H1>0,(And(DATE(YEAR(H1)+2,MONTH(H1),DAY(H1),"REUSE""")))

    If I remove one of the quotes at the end of the formula, I receive error message "The formula you typed contains an error".
    Code is:
    =IF(H1>0,(And(DATE(YEAR(H1)+2,MONTH(H1),DAY(H1),"REUSE"")))

    Attached is an example:
    Attached Images Attached Images
    Last edited by hayestrent; 11-04-2014 at 02:21 PM.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: If date older than 2 years, insert Reuse in formula cell

    why have you not used the formula I posted ?

    =IF(AND( H13>0, DATE(YEAR(H13)+2,MONTH(H13),DAY(H13) ) ) ,"REUSE","")

    Your formula for IF is incorrect hence the error , as you have too many arguments in that IF
    IF( test, Value_for_true, value_for_false)


    the formula I posted would NOT return that error ?
    Hence why I asked what the error was

  9. #9
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If date older than 2 years, insert Reuse in formula cell

    The formula you gave me returns "Reuse" on dates that are not 2 years old. Any ideas?

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: If date older than 2 years, insert Reuse in formula cell

    sorry I did not look close enough to the date formula only the IF syntax

    =IF(AND(H13>0,TODAY()>=DATE(YEAR(H13)+2,MONTH(H13),DAY(H13))),"REUSE","")

    if you do not want 4/11/12 to be include - which is the date at the time of posting
    then remove the =
    =IF(AND(H13>0,TODAY()>DATE(YEAR(H13)+2,MONTH(H13),DAY(H13))),"REUSE","")

  11. #11
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If date older than 2 years, insert Reuse in formula cell

    It works perfectly!! Thank you.

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: If date older than 2 years, insert Reuse in formula cell

    your welcome
    if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

+ 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] Using Countif and Subtotal based on date older than 2 years ago
    By simmy981 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-10-2014, 04:29 AM
  2. Replies: 2
    Last Post: 04-23-2014, 09:07 AM
  3. [SOLVED] Date older than 3 years? (yes/no)
    By wintheranders in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-19-2013, 06:00 AM
  4. [SOLVED] Conditional Formatting. Anything 4.5 years old + older from todays date needs Highlight.
    By themanwithnoshoes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2012, 11:58 AM
  5. Highlighting dates older than 4 years
    By james1234 in forum Excel General
    Replies: 2
    Last Post: 04-04-2010, 07:15 AM

Tags for this Thread

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