+ Reply to Thread
Results 1 to 29 of 29

Coloring Cells by Date of Birth

  1. #1
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Coloring Cells by Date of Birth

    My principal recently asked me to help with our student list that in Excel. I had to put a formula in that sorted every student by how old they were, years, months and days. They are now requesting that the column that has that information be color coded. Can someone please help? Every time I put a new rule in Conditional Formatting nothing works. I can gladly share the file with anyone who can guide me in the right direction.

    Thanks in advance.

    Jon

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    Hello and welcome to the forum.

    I recommend uploading a small representative sample of your data along with the desired results (which you can fill manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Coloring Cells by Date of Birth

    Colour coded based on what?
    Here's some examples,

    Student is over 70 years old - colour the cell as green.

    This is clearly ridiculous as I doubt any students are over 70 years old.

    So you need to clearly define how the information is to be colour coded.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Attached is the worksheet. I'm also not figuring out why H71 is not reading the formula and only coming up with #VALUE!

    We have to color code the spreadsheet to where any student over 18 has a transparent red cell and any student under 14 is in a yellow cell.

    Again, any help is greatly appreciated.
    Last edited by jneiderman82; 06-10-2019 at 10:35 AM.

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by jneiderman82 View Post
    Attached is the worksheet. I'm also not figuring out why H71 is not reading the formula and only coming up with #VALUE!
    B71 is text not a date.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    I asked for a sample, not actual student data with their birthdays.

    I HIGHLY recommend editing your file and removing the names (you're sharing this on the internet)

    I'm also not figuring out why H71 is not reading the formula and only coming up with #VALUE!
    B71 has a space in front of the 6. Remove that and the formula in H71 will work as expected.

  7. #7
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by kersplash View Post
    B71 is text not a date.
    Thank you, worked like a charm!

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Coloring Cells by Date of Birth

    Select the range to highlight

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =DATEDIF(B5,TODAY(),"y") >= 18
    format as red

    =DATEDIF(B5,TODAY(),"y") <= 14
    format as yellow
    Last edited by Special-K; 06-10-2019 at 10:29 AM.

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Coloring Cells by Date of Birth

    You can use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to highlight anyone who is 18

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    As for the Conditional Formatting, I'm assuming that you want everyone 18 or older (not just over 18) to be in red.

    Highlight A5:H1000 (or however far down you may need to go) > Conditional Formatting > New Rule > Use a formula
    =AND(DATEDIF($B5,TODAY(),"y")>=18,ISNUMBER($B5))
    Format: Fill red > OK > OK


    Highlight A5:H1000 (or however far down you may need to go) > Conditional Formatting > New Rule > Use a formula
    =AND(DATEDIF($B5,TODAY(),"y")<14,ISNUMBER($B5))
    Format: Fill yellow > OK > OK

  11. #11
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Thank you everyone! The formula for 14 and under worked great. I'm still having an issue with the formula for 18 and over.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Coloring Cells by Date of Birth

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Attached is an edited version of my spreadsheet, can't believe I shared the original

    I input the formula for the 14 and under. I'm having trouble color coding the 18 and older to red.

    I appreciate all the help and support thus far. Thank you all again!
    Attached Files Attached Files

  14. #14
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    See attachment.

    I have implemented the steps outlined in post #10 to your original sheet (and have removed the names).
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by 63falcondude View Post
    See attachment.

    I have implemented the steps outlined in post #10 to your original sheet (and have removed the names).
    Thank you again! What was I inputting the formula for over 18 wrong? I see that yours worked with no issues.

  16. #16
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    To check, I'll open the workbook that you shared in post #13, click on any cell that has CF applied to it, then go to Conditional Formatting > Manage Rules.

    It looks like there are 4 rules applied here. I'm going to delete that last one.

    Now, hovering over the first one shows that 18 is not included (we want >=18 not >18) so I'll delete that one too.

    Now, select the only red rule left and click on Edit Rule. Change both instances of $B1048397 to $B5 then select OK > OK and you should be good to go.

  17. #17
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by 63falcondude View Post
    To check, I'll open the workbook that you shared in post #13, click on any cell that has CF applied to it, then go to Conditional Formatting > Manage Rules.

    It looks like there are 4 rules applied here. I'm going to delete that last one.

    Now, hovering over the first one shows that 18 is not included (we want >=18 not >18) so I'll delete that one too.

    Now, select the only red rule left and click on Edit Rule. Change both instances of $B1048397 to $B5 then select OK > OK and you should be good to go.

    I was curious if you could potentially help me with another issue. When I changed one of the rules for my worksheet a lot of the cells were highlighted that should not have been highlighted. See attached for my example.

    This problem starts at Row 51. Thanks again in advance.
    Attached Files Attached Files

  18. #18
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    You have a lot of unneeded CF there.

    Select all of the cells by clicking on the triangle above row 1 and to the left of column A > Conditional Formatting > Manage Rules > Delete all rules except for the first yellow and red rules.

    Now you should be left with two rules.

    Change the "Applies to" range to =$B5:$H1000 then select OK

    If you don't want the whole row (from B to H) highlighted, you can use this instead in the "Applies to" range: =$B5:$B1000,$H5:$H1000

  19. #19
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    It worked great! Thank you again, this forum is such a valuable resource!
    Last edited by jneiderman82; 06-11-2019 at 01:41 PM.

  20. #20
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    My pleasure. Happy to help.

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

  21. #21
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Is there a way that will update the age background color based on today's date and lets say a future date? If I was to alter the cell that has the date in it based on the formula, the updated cells don't get highlighted red, if they are over 18. I thought they would but maybe I'm missing something.

  22. #22
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    As it sits, the conditional formatting rules calculate the age based on the current date and the date of birth in column B.

    =AND(DATEDIF($B5,TODAY(),"y")>=18,ISNUMBER($B5))

    =AND(DATEDIF($B5,TODAY(),"y")<14,ISNUMBER($B5))

    For example, if you were to change the DOB in B5 to 12/11/2000, that person will now be 18 and so that row will now turn red.

    See attachment.
    Attached Files Attached Files
    Last edited by 63falcondude; 06-11-2019 at 02:12 PM.

  23. #23
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    In that same sheet, what if we were to change the date of F2 to a later date, why is that not changing the color in column H?

  24. #24
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    As mentioned in post #22, the Conditional Formatting rules calculate the current age. Not the age as of the date in F2.

    If you want the ages to be based on the date in F2, you can swap TODAY() in the CF formulas with $F$2.

    Edit: I now see that the formulas in column H were also based on F2 and not today's date.
    Last edited by 63falcondude; 06-11-2019 at 02:28 PM.

  25. #25
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Coloring Cells by Date of Birth

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by 63falcondude View Post
    As mentioned in post #22, the Conditional Formatting rules calculate the current age. Not the age as of the date in F2.

    If you want the ages to be based on the date in F2, you can swap TODAY() in the CF formulas with $F$2.

    Edit: I now see that the formulas in column H were also based on F2 and not today's date.
    Swapping the CF Formula with $F$2 took the fill color out. I'm sorry in advance for all the questions. Just trying to finalize this issue.

  27. #27
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    Select cell B5 > Conditional Formatting > Manage Rules > select the red rule > Edit Rule > Change formula from:

    =AND(DATEDIF($B5,TODAY(),"y")<16,ISNUMBER($B5))

    to

    =AND(DATEDIF($B5,$F$2,"y")<16,ISNUMBER($B5))

    Do the same for the yellow rule.

    Attached is the result from doing this.
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    06-10-2019
    Location
    Nyack, NY
    MS-Off Ver
    Windows 10 Home
    Posts
    19

    Re: Coloring Cells by Date of Birth

    Quote Originally Posted by 63falcondude View Post
    Select cell B5 > Conditional Formatting > Manage Rules > select the red rule > Edit Rule > Change formula from:

    =AND(DATEDIF($B5,TODAY(),"y")<16,ISNUMBER($B5))

    to

    =AND(DATEDIF($B5,$F$2,"y")<16,ISNUMBER($B5))

    Do the same for the yellow rule.

    Attached is the result from doing this.
    Worked like a charm! Thank you again for all your help!!!

  29. #29
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Coloring Cells by Date of Birth

    You're welcome.

+ 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. Coloring cells based on date range and adding a shape at a certain date
    By moomphas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2018, 05:17 AM
  2. Date of Birth/Age/Today's Date and Blank Cells
    By jdawg1536 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-14-2015, 10:49 PM
  3. VBA coloring of cells on the basis of information about the start and end date
    By warden792 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2015, 05:43 AM
  4. Work out Term of Birth by Date of Birth
    By sezza79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2014, 11:06 AM
  5. Excel 2007 : Merge cells & todays age/ date of birth
    By andy9988 in forum Excel General
    Replies: 5
    Last Post: 09-08-2010, 12:08 AM
  6. Change the Coloring of Cells based on date
    By DocH in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2009, 07:51 AM
  7. Converting Date of Birth to 'Term of Birth'
    By Kungfusal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-26-2009, 04:12 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