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

1. 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. 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)

3. 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. Re: Not so excel savvy- Need to conditional format specific dates in a range.

Perhaps post a sample sheet? (no pics please)

5. 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.

6. 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. 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. 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))

9. 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. 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

11. 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.

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

Originally Posted by Seasons23
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)

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

13. 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. 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. 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. 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. Re: Not so excel savvy- Need to conditional format specific dates in a range.

Originally Posted by ChemistB
=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. 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

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