Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Excel Formula Help

D

davidjearly

macrumors 68020
Original poster
Sep 21, 2006
2,271
382
Glasgow, Scotland
Hi,

Wondering if you guys can help.

I'm currently working on something in Excel, which has a list of names, and dates or birth.

Based on the column which contains the birth dates, is it possible to make it so that a cell in that column will turn red when it means the person is 19 or younger?

If so, how?

I know about conditional formatting and that but can't work out how to get Excel to do this without creating an age column, which I don't want to do.

Thanks,
David
 
davidjearly said:
Hi,

Wondering if you guys can help.

I'm currently working on something in Excel, which has a list of names, and dates or birth.

Based on the column which contains the birth dates, is it possible to make it so that a cell in that column will turn red when it means the person is 19 or younger?

If so, how?

I know about conditional formatting and that but can't work out how to get Excel to do this without creating an age column, which I don't want to do.

Thanks,
David
Click to expand...

Which version of Excel are you using? Mac/Windows and year? You can use a formula to perform the conditional formating and just look at the date values compared to today.
 
reclusivemonkey said:
Which version of Excel are you using? Mac/Windows and year? You can use a formula to perform the conditional formating and just look at the date values compared to today.
Click to expand...

To dot his particular task, I'm using the Windows version of Excel 2003. However, if possible I would like the formula to work on the Mac version too, although this is not essential.

Thanks for your input,

David
 
Code: 
=IF(YEAR(TODAY())-YEAR($A1)<=19,IF(AND(DAY($A1)<=DAY(TODAY()),MONTH($A1)>=MONTH(TODAY())),TRUE,FALSE),TRUE)=TRUE

This presumes your date is in cell A1. Just add the conditional format to that cell using "Formula is", then use the format painter to apply it to the rest of the cells. This is using the formulas in Excel 2008; you might be able to do something more elegant in Excel for Windows, but Excel isn't great with dates and times.
 
Stupid Excel. It would have been so easy with the YEARFRAC funtion, but I get an error message when using it with conditional formatting.

Edit: Oh, and the above formula is a good starting point to do it yourself, but doesn't work properly yet. With the birthdate 1989-01-01 the formula gets FALSE although the person is 19 in fact.
 
reclusivemonkey said:
Urgh, yeah ignore mine that's a much more elegant way of doing it.

Code: 
=IF(YEARFRAC(A6,TODAY())>=19,TRUE,FALSE)=TRUE

Works just fine for me in Excel 2008 for Mac.
Click to expand...
Good to hear, doesn't work for me in Excel 2003 for Windows though (in conditional formatting).

Edit: I see the OP does also use Excel 2003. It's still worth a try though, perhaps I did something wrong.
 
Have a look at the Microsoft Help for the YEARFRAC function.

Two things:

You need the Analysis ToolPak Add-in
The Dates need to be entered using the DATE funciton, not just text
 
Hmmm... I can't get either of those to work.

The first formula can be added fine in the conditional format dialog box, but does not make the cell go red when the date of birth of someone under 19 is entered.

The second formula gives an error saying that I can't reference cells in another worksheet or workbook, despite the fact that the formula does not seem to contain any such references. I did install the Analysis Toolpak addin also.

Any other ideas? This is where I wish my Excel knowledge was a little more advanced!

David
 
davidjearly said:
Hmmm... I can't get either of those to work.

The first formula can be added fine in the conditional format dialog box, but does not make the cell go red when the date of birth of someone under 19 is entered.

The second formula gives an error saying that I can't reference cells in another worksheet or workbook, despite the fact that the formula does not seem to contain any such references. I did install the Analysis Toolpak addin also.

Any other ideas? This is where I wish my Excel knowledge was a little more advanced!

David
Click to expand...
As I said the first formula is a good starting point (it's nothing else than calculating the age) but doesn't work in all cases. It still needs some "fine tuning".

The error you get with the second formula is exactly the one I got. This seems to be a bug in this version of Excel that is later resolved. So you have to use the first formula and figure out what's not correct yet.
 
davidjearly said:
Yep, I've been trying to get this working for hours now! Argh.

Any other input?
Click to expand...
Well, I'm afraid you will have to calculate the age. There seems to be no way around it. I had hoped that you needn't make an extra column for it, but instead make the calculation inside the conditional formatting, but the formula I use to calculate the age from two given dates unfortunately is too long for this as I just found out by trying myself. Either you construct a short enough one, or you will need to add a column for this. At least you could hide the column, if that helps anything...

Edit: So maybe I found a formula that will work. I tried to change mine to using IF-functions to shorten it and now have one that worked with some testing. I am not sure if it is really correct in all cases, but I leave it to you to find it out ;) Just put the following formula in the conditional formatting (assuming you have the birthdate in A1):
=IF(YEAR(TODAY())-YEAR(A1)-IF(MONTH(A1)>MONTH(TODAY());1;0)-IF(MONTH(A1)=MONTH(TODAY());IF(DAY(A1)>DAY(TODAY());1;0);0)<=19;TRUE;FALSE)
Click to expand...

Let me know if this works.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back