+ Reply to Thread
Results 1 to 7 of 7

IF/THEN: date is 5 years older than date in other cell

  1. 03-28-2018, 01:49 PM #1
    brittanyalysse
    brittanyalysse is offline
    Registered User
    Join Date
    03-28-2018
    Location
    Jacksonville, Florida
    MS-Off Ver
    365 (2016)
    Posts
    1

    IF/THEN: date is 5 years older than date in other cell

    I am a graduate student who can't figure out how to set up a formula! I have one column with birthdays and another column with dates that the child entered a program. I have a third column that I want to say YES if the child entered the program before age 5 and NO if the child entered the program after age 5. Does anyone know how to create a formula for this? Thanks for your help!
    Reply With QuoteRegister To Reply
  2. 03-28-2018, 02:03 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: IF/THEN: date is 5 years older than date in other cell

    If your dates are in column A and B:

    =IF(DATE(B1 < DATE(YEAR(A1)+5, MONTH(A1), DAY(A1)), "YES", "NO")

    If the birthday is in A1, then DATE(YEAR(A1)+5, MONTH(A1), DAY(A1)) gives the 5th birthday.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 03-28-2018, 02:04 PM #3
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,501

    Re: IF/THEN: date is 5 years older than date in other cell

    you could use a datedif formula for that.
    something like =IF(DATEDIF(A1,B1,"y")>=5,"Yes","No") where A has the birth date and B has the date they entered the program.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 03-28-2018, 02:08 PM #4
    avk
    avk is offline
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,223

    Re: IF/THEN: date is 5 years older than date in other cell

    Suppose column "A" is Birthday. Column "B" Date.
    In column "D" helper column with formula : =INT(B2-A2)/365 copy down
    In column "C2" :
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    atul

    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
    Reply With QuoteRegister To Reply
  5. 03-28-2018, 02:10 PM #5
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: IF/THEN: date is 5 years older than date in other cell

    Quote Originally Posted by avk View Post
    =INT(B2-A2)/365 copy down
    This solution does not take leap years into account.
    Reply With QuoteRegister To Reply
  6. 03-28-2018, 03:26 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: IF/THEN: date is 5 years older than date in other cell

    Removed by FR. Wrong formula.
    Last edited by FlameRetired; 03-28-2018 at 03:28 PM.
    Dave
    Reply With QuoteRegister To Reply
  7. 03-28-2018, 03:30 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: IF/THEN: date is 5 years older than date in other cell

    Try this in C2 and filled down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    A
    B
    C
    1
    BD
    Entered
    2
    1/18/2015
    5/21/2016
    YES
    3
    7/13/2016
    8/7/2017
    YES
    4
    5/30/2014
    5/18/2015
    YES
    5
    9/19/2012
    12/1/2015
    YES
    6
    1/22/2016
    1/12/2018
    YES
    7
    1/23/2012
    2/23/2016
    YES
    8
    1/6/2013
    9/18/2013
    YES
    9
    4/16/2016
    8/12/2017
    YES
    10
    11/28/2012
    10/13/2016
    YES
    11
    8/17/2012
    10/23/2014
    YES
    12
    3/27/2013
    1/16/2016
    YES
    13
    1/15/2013
    1/19/2018
    NO
    14
    3/22/2017
    10/18/2017
    YES
    15
    12/27/2012
    1/15/2018
    NO
    16
    10/10/2016
    6/26/2017
    YES
    17
    2/13/2017
    10/28/2017
    YES
    18
    10/22/2012
    2/22/2018
    NO
    19
    2/23/2017
    11/10/2017
    YES
    20
    4/26/2013
    6/22/2014
    YES
    21
    1/16/2013
    9/20/2016
    YES
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Highlight a cell with a date which is coming upto 5 years from a fixed date
    By ItsAllDinx in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2015, 05:33 PM
  2. Delete Rows if Date in Column A is Older Than 6 Years
    By BantamPCI in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2014, 06:13 AM
  3. [SOLVED] If date older than 2 years, insert Reuse in formula cell
    By hayestrent in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-04-2014, 02:44 PM
  4. [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
  5. [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
  6. [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
  7. Adding years to a date, but leaving blank if no date in the original cell.
    By buddyhackit9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2011, 01:17 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1