+ Reply to Thread
Results 1 to 18 of 18

Not so excel savvy- Need to conditional format specific dates in a range.

  1. #1
    Registered User
    Join Date
    02-24-2014
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2010
    Posts
    20

    Not so excel savvy- Need to conditional format specific dates in a range.

    I have a spreadsheet that is used for monitoring birthdays for children that are under 2. I need to find a way to automatically highlight children who have a birthday falling in the current week's date range. For example, using the current week, M-F 02/24-02/28/14, I need all the children with birthdays this week that are turning one or two, so anyone with a 2012 or 2013 birthday from 02/24-02/28. Is there a formula or a conditional highlighting that I can use to avoid going row by row to monitor the birthdays?

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Must be an easier way but assuming your dates are in A1:A40, select that range and conditional formatting>use formula

    =DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) -WEEKDAY(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),2)+1=TODAY()-WEEKDAY(TODAY(),2)+1
    format as green (or whatever)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-24-2014
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Well I'm not sure what exactly I need in order to do that. Whenever I see the formulas, my brain short circuits. I was going to insert a screen shot, but by the time I crop everything out that can't be shared it's pretty pointless. The dates are in column F, how I would I enter the formula specifically if I wanted to highlight the dates for this current week? I can adjust a formula once I understand exactly where everything is supposed to go, but I got lost trying to enter the one you provided.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Perhaps post a sample sheet? (no pics please)

  5. #5
    Registered User
    Join Date
    02-24-2014
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Thank you for that idea. I've attached a sample of what I'm working with. At the top of the sheet is the current week. I change this every week to reflect the current date. Each child has their own row with their specific information. I need a way to have the birthdate highlight whenever it falls between the dates of the current week, but only for those children under 2.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    The formula I gave is for Conditional Formatting (which you can find on your Excel Home tab)
    First select the cells that your dates are in. In my example, they were in A1:A40. Then
    Conditional Formatting>New Rule>"Use formula..."
    Then paste the formula in the text box in the conditional formatting window and click on Format and adjust your fill color.
    Adjust the formula to represent the first cell in your selected range. I.e. if your data is in B2:B1000, you'd replace A1 with B2.
    Hope that helps.

  7. #7
    Registered User
    Join Date
    02-24-2014
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Ok I tried that, and it was close to what I need, but it highlighted dates I didn't need and didn't get the ones I needed. Very close though. Thank you for helping.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    It's much simpler if you can separate start and end dates into separate cells for current week, e.g. with start date in L1 and end date in M1 you can use this formula in conditional formatting

    =((EDATE(F3,12)>=L$1)*(EDATE(F3,12)<=M$1))+((EDATE(F3,24)>=L$1)*(EDATE(F3,24)<=M$1))
    Attached Files Attached Files
    Audere est facere

  9. #9
    Registered User
    Join Date
    02-24-2014
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    That works really well. However when I tried to apply it to a spreadsheet with 213 rows, it didn't highlight anything. What do I need to adjust in the formula?

  10. #10
    Registered User
    Join Date
    02-07-2014
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Hello,

    you have to be very careful when selecting the rows you want for conditional formatting.
    Conditional formatting is not forgiving.
    Its important that the first cell referred to in the Formula, is also the same first cell referred to in the Conditional Format selection.
    i.e. in the attached example, the first cell in the formula refers to E1. The conditional Format refers to $E:$E, (the first cell being E1 then)

    I have uploaded a variant on ChemistB's solution, using WEEKNUM formula to cut down on the length of Formula.
    (although I had to use his formula to help debug mine :o)

    =IF(WEEKNUM(DATE(YEAR(TODAY()),MONTH(E1),DAY(E1)))=WEEKNUM(TODAY()),TRUE,FALSE)

    Cheers
    Tim
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Here's your example with my formula used on Column F (setting the formatting to Green w/ Bold font). It appears to be working properly. Use Conditional Formatting>Manage Rules to look at the formula.
    Attached Files Attached Files

  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Quote Originally Posted by Seasons23 View Post
    What do I need to adjust in the formula?
    You need to take on board what Tim says about matching the formula to the "applies to" range. The way I usually do it is to select the range of dates first....and then use the formula which refers to the first cell in that range (F3 in your earlier example)

    Quote Originally Posted by Seasons23 View Post
    ...but only for those children under 2....
    Hello ChemistB and Tim, neither of your suggestions will take the age in to account

  13. #13
    Registered User
    Join Date
    02-24-2014
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    I'm decent enough with the conditional formatting as I use it in other aspects of the spreadsheet. I'm not great, but I've learned to do what I need. I've tried the formulas given and I've selected the cells that I need to apply the formatting to, but it doesn't select the dates I need. So I'm not sure what I'm doing wrong...

  14. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Good point, DDL
    Using Tim's formula (except without the IF/TRUE/FALSE, which isn't needed)

    =AND(WEEKNUM(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3)))=WEEKNUM(TODAY()), DATEDIF(F3,TODAY(),"y")<3)

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    So I'm not sure what I'm doing wrong...
    Did you look at the conditional formatting in the example I uploaded? I suggest, trying it yourself on the example, then compare the rule with the rule in my sheet. Maybe that would help.

  16. #16
    Registered User
    Join Date
    02-24-2014
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    I copied the formula in the example you gave ChemistB, but when I try to apply to my actual spreadsheet it did and didn't work. I first applied it and it did highlight the birthdays that fell within this week's range, but it didn't limit it to children/birthdays under two, and then I couldn't figure out how to adjust it to use it for a different range of dates. I would have to be able to essentially copy and paste the sheet each week and change the dates at the top and the cells magically highlight with the children 2 and under with birthdays that week. I'm not sure if I'm asking too much of Excel.

  17. #17
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Quote Originally Posted by ChemistB View Post
    =AND(WEEKNUM(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3)))=WEEKNUM(TODAY()), DATEDIF(F3,TODAY(),"y")<3)
    This may still format some wrong DOBs, e.g. today (25th Feb) that will format 26-Feb-2011, although the child will be 3 on that date......

    I'd also be reluctant to use WEEKNUM in this type of formula - what if you are looking at the week of Monday 29th December 2014 to Friday 2nd January 2015, WEEKNUM won't return the same result for all days in that week.

  18. #18
    Registered User
    Join Date
    03-06-2014
    Location
    Pongola, RSA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Not so excel savvy- Need to conditional format specific dates in a range.

    Wow a lot of excel savvy guys on this thread!!! I have a similar problem a bit complex though. I am sure if you guys put your heads together you can help me. Please... My current post can be found at
    http://www.excelforum.com/excel-form...ml#post3611949

+ 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. Excel 2007 Conditional Format date range
    By thomasoj01 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-25-2014, 05:49 PM
  2. Replies: 6
    Last Post: 07-01-2012, 05:10 PM
  3. Replies: 2
    Last Post: 06-04-2012, 12:29 PM
  4. Conditional Format Dates in a Calender when Matches dates in a list
    By Lungfish in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2009, 06:23 AM
  5. Replies: 3
    Last Post: 12-17-2008, 09:20 PM

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