Excel Formula Help

Discussion in 'Mac Apps and Mac App Store' started by davidjearly, Dec 5, 2008.

  1. davidjearly macrumors 68020

    davidjearly

    Joined:
    Sep 21, 2006
    Location:
    Glasgow, Scotland
    #1
    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
     
  2. reclusivemonkey macrumors 6502

    reclusivemonkey

    Joined:
    Jun 2, 2008
    Location:
    Sowerby Bridge, West Yorkshire, UK
    #2
    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.
     
  3. davidjearly thread starter macrumors 68020

    davidjearly

    Joined:
    Sep 21, 2006
    Location:
    Glasgow, Scotland
    #3
    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
     
  4. reclusivemonkey macrumors 6502

    reclusivemonkey

    Joined:
    Jun 2, 2008
    Location:
    Sowerby Bridge, West Yorkshire, UK
    #4
    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.
     
  5. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #5
    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.
     
  6. reclusivemonkey macrumors 6502

    reclusivemonkey

    Joined:
    Jun 2, 2008
    Location:
    Sowerby Bridge, West Yorkshire, UK
    #6
    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.
     
  7. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #7
    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.
     
  8. tiguk macrumors 6502

    Joined:
    Jun 12, 2008
    Location:
    UK
    #8
    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
     
  9. davidjearly thread starter macrumors 68020

    davidjearly

    Joined:
    Sep 21, 2006
    Location:
    Glasgow, Scotland
    #9
    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
     
  10. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #10
    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.
     
  11. davidjearly thread starter macrumors 68020

    davidjearly

    Joined:
    Sep 21, 2006
    Location:
    Glasgow, Scotland
    #12
    Yep, I've been trying to get this working for hours now! Argh.

    Any other input?
     
  12. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #13
    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):
    Let me know if this works.
     

Share This Page