Can you teach me the 'If' commands in Excel?
I need a formula that will give us a person’s age when we plug in their month, day and year of birth. Subtracting the current year from their birth year gives you an idea of how old they are, but it’s not an exact number unless you factor in the month and the day and I don’t know how to do that.
I’ve been wanting to learn the ‘if’ commands but just never got around to it. I’m ready now, Teach! Bring it on!
Thank you. : )
Observing members:
0
Composing members:
0
28 Answers
You don’t really need an IF function here. Let me introduce you to another one that’s great for use with dates: DATEDIFF, which gives the difference between dates depending on the parameter you select. Use “y”, “m” or “d”. (You might even be able to use “h” for hours, for all I know. Play with that.)
=datediff( OLDER date, MOST RECENT date, “m”)
When it works, this will give you the difference in months “m” between Old date and New date. You can divide the answer by 12 to get the years and decimal years’ difference. (You could also use the “y” parameter instead of “m” to get “whole years”, but that won’t give you the remainder that you seem to want.)
If the function doesn’t work for you at first, or you get a NAME? error, then you’ll need to activate the Add-in that contains this function, which is very simple. Send a follow-up message if you need that assistance.
Need that assistance. Getting name error.
OK. =datediff( OLDER date, MOST RECENT date, “m”)
I have a birthdate of 07 24 1982. I have the 07 in one cell, 25 in a 2nd, and 1982 in a third. Same with 10 06 2011. So I just replace OLDER DATE with the first three cells, 07 25 1982 and NEWER DATE with the three cells containing today’s date? Not sure what to put in the “m” variable….
so it looks llike =datediff(A1:A3,B1:B3, ”?”) I don’t have a question mark…I just don’t know what to put there.
Where are you? WHERE ARE YOU??? WHEREAREYOU? WHEREAREYOU????????
patiently waiting here…reading Upton Sinclair’s ‘The Jungle’....where are you?
Fine. Going to the back deck to read. I’ll be back. :( Sniff.
you should have the persons date of birth in one cell like this 12/31/1990, then your formula should look like =datedif(A1,today(),“y”) today() will always update to be the current date which sounds like what you were looking for.
No need to separate out the dob into 3 cells and use “y” to return the number of years.
Yeah, what @abysmalbeauty said. You have to somehow read “a single value” as a date.
You could maintain separate cells for Month Day and Year of birth (let’s say A1, B1, C1 for M D and Y), and your formula would be (this is ugly, but it will work):
=DATEDIF( DATE( C1, A1, B1), NOW(), “m”) / 12 to get the age of a person born on A1 / B1 / C1 in “decimal years” up to “right now”.
But that’s not the easiest way to do it. Best would be to concatenate the separate values into a single cell somewhere using the = Date( year, month, day) formula, view the dates in that column to see that they make sense (that you haven’t transposed years and days, for example – months and days will show an error condition whenever you try to have a “month > 12”), and then use that cell as the single “birth date” reference cell in the formula.
I think I’m following ya’ll…but I’m still stuck on using the =datediff( OLDER date, MOST RECENT date, “m”) and I don’t know what goes in the “m” or where to get it. Forgive my ungeekness.
What I have now is A1 (month, day and year of birth) is set to ‘date’ 01/01/01. B1 is set to current ‘date’ 01/01/01. So I plugged A1 and B1 into my ‘OLDER DATE, MOST RECENT DATE.” So where do I find the ‘m’?
And what the hell does “concatenate” mean??? I suck as a geek! Don’t tell Rarebear!
The “m” is literal. Use the letter M enclosed in double quotes. What the formula means is:
Give me the date difference FROM this old date TO this future date [future in relation to the old date, anyway] and give me that difference in Months.
If you used the “Y” parameter then you’d get “full years”. Or the “D” parameter to get “full days”.
You may not know it, but Excel can try to give you help on formulas. Do this:
1. In a cell, start typing =datedif(
2. When you’ve gotten that far, hit Ctrl-A.
3. Excel will put up a dialog box at the top of your screen and prompt you through the various parts of the formula that you need to enter.
4. As you fill in entries in the dialog box cells, Excel will show on the right side of the dialog how it’s attempting to interpret those entries.
No..no help there. Did I mention that here at home I’m using Windows 97? At work I’m using Windows7. Lord, I wish I could send screen prints…
Well, you can put screen prints on Flickr or any other photo sharing page. (I use PicPick to take the screen prints, but there are tons of free utilities to do that.)
What kind of problem are you having?
I know…but my home computer is slooooow to upload to Photobucket and everything else. I have to go to work in the morning!
Problem at the moment is, when I type in datediff( and hit ctrl A, there is no help thing that pops up.
Are you typing the ”=”?
Type
=datedif(
then hit Ctrl-A.
Can you copy and paste the formula you have in there to fluther and we can just fix it so you can copy it back to the sheet lol
I realized you only had one f in dif, so I tried that. Ctrl A highlights the formula bar, a message comes up that says ‘choose the help button for help on this function and its arguments.’
Then it says ‘This function takes no arguments’
Then it says ‘Formula result = and then it’s blank.
@abysmalbeauty I have a better idea! Just type in the exact formula I need, using the parameters of birthday 07 25 1985 and current date of Oct 6 2011, and I’ll copy THAT into Excel!
in cell A1 put this exactly: 07/25/1985
In cell A2 put this exactly: 10/06/2011
then use formula
=datedif(A1, A2, “y”)
Do you always want to know the age based on yesterday for the rest of time?
this is another option but it would be more difficult to use because you’d have to alter the dates inside the formula every time you use it. It would work for one time use without taking up extra cells though
=DATEDIF(DATE(1985,7,25), DATE(2011, 10, 6), “y”)
I wish I had an excel project to do…. :( Im bored
No. I want the age of the person listed when you type in the current date as you’re entering them in a data base.
Got a Name error.
FINE! Your mission, should you choose to accept it is: Make me an excel spreadsheet with that formula and email it to me! That should keep you busy for .5 minutes!
how do you want it set up, name date age in years?
PS i’m done whenever you would like to have it
Yes, like
Wis | Grog | Dumbass | Address | Birthdate | [auto inserts age when you plug in their birthday.
Yes, I want it NOW!!!
Then go look at the question I asked. You’ll have about 10 minutes of NO boredom!
Thanks @abysmalbeauty! I was waiting and waiting..then thought to check my spam folder! Don’t worry. I don’t think you’re spam!
Thanks again.
Obviously you liked @abysmalbeauty‘s solution better than mine, or you’d have sent Larissa Riquelme instead of those nifty soccer moves on the field. Glad it worked out for you, anyway.
Actually, it was your email that I got and used @CWOTUS. I was getting confused over who was sending what and who I was talking to. THANK YOU!!!
Here’s your present! : )
Answer this question 
Composing members:
0
