Ինչպե՞ս վերափոխել ծննդյան ամսաթիվը արագ Excel- ում:

Օրինակ, Excel- ում դուք ստանում եք ծննդյան ամսաթվի վերաբերյալ մի շարք տվյալներ, և ձեզ հարկավոր է վերափոխել այդ ծննդյան ամսաթիվը ՝ Excel- ում դրանց ճշգրիտ տարիքային արժեքը ցուցադրելու համար, ինչպե՞ս կցանկանայիք պարզել: Այս հոդվածում թվարկված են մի քանի խորհուրդներ Excel- ում ծննդյան ամսաթիվը հեշտությամբ տարիքի դարձնելու համար:

Մեթոդ Ա.
Convertննդյան ամսաթիվը բանաձեւերով փոխեք տարիքին

Մեթոդ Բ.
Հեշտությամբ փոխեք ծննդյան ամսաթիվը տարիքի ՝ առանց բանաձևեր հիշելու

Convertննդյան ամսաթիվը բանաձեւերով փոխեք տարիքին

Ստորև ներկայացված բանաձևերը կարող են օգնել Excel- ում ծննդյան ամսաթվի վրա հիմնված տարիքի հաշվարկը:

Convertննդյան ամսաթիվը փոխեք INT- ի գործառույթով

INT գործառույթը կարող է օգնել հաշվարկել անձի տարիքը ՝ ելնելով տվյալ ծննդյան ամսաթվից, խնդրում եմ արեք հետևյալ կերպ.

1. Ենթադրելով, որ գոյություն ունեն ծննդյան ամսաթիվը և ներկայիս ամսաթիվը առանձին պարունակող երկու սյունակ:

2. Տարիքը թողարկելու համար ընտրեք դատարկ բջիջ, մուտքագրեք ներքևի բանաձևը և սեղմեք այն Մտնել բանալի. Ընտրեք արդյունքի բջիջը և ապա քաշեք այն Լրացրեք բռնակ ներքև ՝ բոլոր արդյունքները ստանալու համար:

=INT((B2-A2)/365)

ATննդյան ամսաթիվը փոխեք տարիքին ՝ DATEDIF գործառույթով

Ստորև ներկայացված DATEDIF գործառույթը կարող է նաև օգնել:

Ընտրեք դատարկ բջիջ `տարիքը թողարկելու համար, մուտքագրեք ներքևի բանաձևը և սեղմեք այն Մտնել բանալի. Ընտրեք արդյունքի բջիջը և ապա քաշեք այն Լրացրեք բռնակ ներքև ՝ բոլոր արդյունքները ստանալու համար:

=DATEDIF(A2,NOW(),"y")

OUննդյան ամսաթիվը վերափոխեք ROUNDDOWN գործառույթի

Փորձեք ROUNDDOWN գործառույթը `ծննդյան օրվա հիման վրա տարիքը հաշվարկելու համար, ինչպես ստորև:

=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)

Theուցադրեք տարիքը որպես Տարի + ամիս + Օր ձևաչափ DATEDIF գործառույթով

Եթե ցանկանում եք տարիքը ցուցադրել որպես Տարի + ամիս + Օր ձևաչափ, փորձեք ստորև նշված DATEDIF գործառույթը:

=DATEDIF(A2,TODAY(),"Y") & " Years, " & DATEDIF(A2,TODAY(),"YM") & " Months, " & DATEDIF(A2,TODAY(),"MD") & " Days"

Հեշտությամբ փոխեք ծննդյան ամսաթիվը տարիքի առանց բանաձեւեր հիշելու

Դուք կարող եք հեշտությամբ ծննդյան ամսաթիվը դարձնել տարիք ՝ առանց հիշելու բանաձևերի հետ Ամսաթիվը և ժամանակը օգնողը of Kutools համար Excel.

Նախքան դիմելը Excel- ի համար նախատեսված գործիքներ, խնդրում եմ նախ ներբեռնեք և տեղադրեք այն.

1. Ընտրեք դատարկ բջիջ `տարիքը թողարկելու համար: Դրանից հետո կտտացրեք Կուտոլս > Բանաձևի օգնական > Ամսաթիվը և ժամանակը օգնողը.

2. Մեջ Ամսաթիվը և ժամանակը օգնողը երկխոսության տուփ, խնդրում ենք կազմաձևել հետևյալը.

2.1) Գնալ դեպի Տարիք էջանիշ;
2.2) Ի Ծննդյան ամսաթիվ տուփ, ընտրեք այն բջիջը, որը պարունակում է ծննդյան ամսաթիվը, որը դուք կվերածեք տարիքի:
2.3) Ընտրեք այն այսօր տարբերակը Դեպի Բաժին;
2.4) Նշեք արդյունքի տեսակը Արդյունքի արդյունքի տեսակը բացվող ցուցակ;
2.5) Կտտացրեք այն OK կոճակը:

Հետո տարիքը բնակեցված է ընտրված խցում: Ընտրեք արդյունքի բջիջը և այնուհետև ամբողջությամբ ներքև քաշեք Լրացնելու բռնակը ՝ բոլոր տարիքը ստանալու համար:

ՆշումԵթե ցանկանում եք տարիքը ցուցադրել որպես Տարի + ամիս + Օր ձևաչափ, ընտրեք Տարի + ամիս + օր - ից Արդյունքի արդյունքի տեսակը բացվող ցուցակ: Եվ արդյունքը կցուցադրվի այնպես, ինչպես ցույց է տրված ստորև նշված էկրանի նկարը: Այն նաև աջակցում է տարիքը ցույց տալ որպես ամիս, շաբաթ կամ օր ՝ ելնելով ձեր կարիքներից:

Եթե ցանկանում եք ունենալ այս օգտակար ծառայության անվճար փորձարկում (30-օրյա), խնդրում ենք կտտացնել այն ներբեռնելու համար, և այնուհետև անցեք գործողությունը կիրառելու վերը նշված քայլերին համապատասխան:

Գրասենյակի արտադրողականության լավագույն գործիքները

Excel- ի համար նախատեսված Kutools- ը լուծում է ձեր խնդիրների մեծ մասը և բարձրացնում ձեր արտադրողականությունը 80% -ով

Վերաօգտագործել: Արագ տեղադրեք բարդ բանաձևեր, գծապատկերներ և այն ամենը, ինչ դուք նախկինում օգտագործել եք. Ryածկագրել բջիջները գաղտնաբառով; Ստեղծեք փոստային ցուցակ և նամակներ ուղարկել ...
Super Formula Bar (հեշտությամբ խմբագրեք տեքստի և բանաձևի բազմաթիվ տողեր); Ընթերցանության դասավորությունը (հեշտությամբ կարդալ և խմբագրել մեծ թվով բջիջներ); Տեղադրել ֆիլտրացված տիրույթում...
Միաձուլել բջիջները / տողերը / սյունակները առանց տվյալների կորստի. Պառակտված բջիջների պարունակությունը; Միավորել կրկնօրինակ տողերը / սյունակները... Կանխել կրկնօրինակ բջիջները; Համեմատեք միջակայքերը...
Ընտրեք Կրկնօրինակ կամ Եզակի Շարքեր; Ընտրեք դատարկ շարքեր (բոլոր բջիջները դատարկ են); Super Find և Fuzzy Find շատ աշխատանքային գրքույկներում; Պատահական ընտրություն ...
Actշգրիտ պատճեն Բազմաթիվ բջիջներ ՝ առանց բանաձևի հղումը փոխելու; Ավտոմատ ստեղծեք հղումներ դեպի մի քանի թերթեր; Տեղադրեք փամփուշտներ, Տուփեր և ավելին ...
Քաղվածք տեքստ, Տեքստ ավելացնել, հեռացնել ըստ դիրքի, Հեռացնել տարածությունը; Ստեղծել և տպել էջային ենթագոտիներ; Փոխարկել բջիջների բովանդակության և մեկնաբանությունների միջև...
Սուպեր զտիչ (պահպանել և կիրառել ֆիլտրի սխեմաները այլ թերթերի վրա); Ընդլայնված տեսակավորում ըստ ամիս / շաբաթ / օր, հաճախականություն և ավելին; Հատուկ զտիչ համարձակ, շեղատառով ...
Միավորել աշխատանքային տետրերը և աշխատանքային թերթերը; Միավորել աղյուսակները ՝ հիմնված հիմնական սյունակների վրա; Տվյալները բաժանեք մի քանի թերթերի; Խմբաքանակի փոխակերպում xls, xlsx և PDF...
Ավելի քան 300 հզոր առանձնահատկություններ, Աջակցում է Office / Excel 2007-2019 և 365. Աջակցում է բոլոր լեզուները: Հեշտ տեղակայում ձեր ձեռնարկությունում կամ կազմակերպությունում: Ամբողջական հատկություններ ՝ 30-օրյա անվճար փորձաշրջան: 60-օրյա գումարի վերադարձի երաշխիք:

Կարդալ ավելին... Անվճար ներբեռնում... Գնեք ...

Office Tab- ը Tabbed ինտերֆեյսը բերում է Office, և ձեր աշխատանքը շատ ավելի դյուրին դարձրեք

Միացնել ներդիրներով խմբագրումը և ընթերցումը Word, Excel, PowerPoint- ով, Հրատարակիչ, Access, Visio և Project:
Բացեք և ստեղծեք բազմաթիվ փաստաթղթեր նույն պատուհանի նոր ներդիրներում, այլ ոչ թե նոր պատուհաններում:
Բարձրացնում է ձեր արտադրողականությունը 50% -ով և նվազեցնում է ձեզ համար ամեն օր մկնիկի հարյուրավոր կտտոցներ:

Կարդալ ավելին... Անվճար ներբեռնում... Գնեք ...

133 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Manoj · 6 years ago
Is there any way to convert age into date of birth. I have a sheet with name and age of customers as if age in years only. Is there any way to have DOB as result
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 6 years ago
  
  Hi Manoj,
  You wouldn't really be able to get the exact date of birth, unless you knew how old they were exactly i.e. 20 years, 50 days. You could calculate it to year by doing something like =YEAR(TODAY())-A1 assuming their current age was in Cell A1.
  Thanks,
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
Pedro · 6 years ago
Its was very easy to follow and all the possible options for me to decide which was the best that accommodate my needs. Thanks :-) ;-) :lol:
- Reply
To post as a guest, your comment is unpublished.
Tanmay Chanda · 6 years ago
Its Very helpful many many thanks.....
- Reply
To post as a guest, your comment is unpublished.
David84* · 6 years ago
Many thanks for this, just got me out of a tight spot at work! Stay classy, excel geniuses.
- Reply
To post as a guest, your comment is unpublished.
Khalil ur Rehman · 6 years ago
=(DATEDIF(A1,TODAY(),"Y")& " Years, " &DATEDIF(A1,TOD AY(),"YM")& " Months, " &DATEDIF(A1,TOD AY(),"MD")& " Days)
is very helpful for me in my office.

Thanks
- Reply
To post as a guest, your comment is unpublished.
AP · 6 years ago
The [i][b]Datedif[/b][/i] function is very helpful& the best....... Thnks a lot
- Reply
To post as a guest, your comment is unpublished.
Dai_Green · 6 years ago
Hi all - Need a bit of help. Please??

I have date of births, and have used the DATEDIF formula to work out the Year and Month of each individual at 2 different points in time.

For Example In May I know Joe is 10 Years, 5 Month but he is only achieving as child of 8 Years, 10 Month. So I want to subtract 1 from the other. So I have an answer of how many Year, Month he is behind. Any Suggestions??

Thank you,

David
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 6 years ago
  
  Hi David,
  Please can you copy and paste in the functions you used and the results so that I know the exact format you're working with here?
  Thanks
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
Carrie Pool · 6 years ago
I have a death date and the exact age at time of death. I need to use this information to calculate a date of birth. Can anyone help me with the formula for this?
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 6 years ago
  
  Hi Carrie,
  
  That's difficult because it may well be a year out without the EXACT age (81.0223 sort of format) as they may well have a birthday in December but you calculate it as at June, meaning it would calculate their age at 81 when in actual fact they're still only 80.
  
  Assuming the age at death is in box A1 and the current date is in B1 (You could replace references to B1 with the =today() function if you'll ony use this ones):
  
  =DATE(YEAR(B1)-A1,MONTH(B1),DAY(B1))
  
  This calculates the given age from the year value of the current date.
  
  Kind regards,
  
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
Juliemichaela · 6 years ago
Can anyone please help?! I have created an Excel spreadsheet. So far so good! Under the 'Client Details' tab are different headings including in column A: Status (the two options being 'client' or purely an 'enquiry'), column B: First Name, column C: Surname, Column D: Email Address and so on.
I have then created a different tab within the same worksheet entitled 'Enquiries'. I want to insert a formula in this tab that will automatically copy across from the 'Client Details' tab the whole line of data for all entries that have a Status of 'Enquiry' displayed in column A.
Can anyone please help?
Many thanks, Julie
- Reply
To post as a guest, your comment is unpublished.
JAGDISH BAUDH · 6 years ago
hi how calculate age column wise exmp- 1- day 2- month 3- years
- Reply
- To post as a guest, your comment is unpublished.
  Thom · 5 years ago
  
  Hi Jagdish,
  
  Could you please clarify what exactly you need to do? Did you want to work out the date but roll it back by 1 day, 2 months and three years? if so, I'd go with method D (Date Difs) to get each one (year, months and days and wrap each datedif within an =sum() and minus the needed difference form each them. Happy to provide an example if you can clarify what exactly you're after :)
  Thanks,
  
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
CI · 6 years ago
Hi, If I use the formulas provide I would need the age to not advance even if they have a birthday tomorrow. For example if I use the formula today 08/15/2014 and they have a birthday of 08/16/2014 I need the spreadsheet to essentially lock or remember the age on 08/15/2014.

Thanks
- Reply
- To post as a guest, your comment is unpublished.
  thomh · 6 years ago
  
  Hi CI,
  
  Put your chosen function into an =if() function like:
  =if(CHOSENFUNCTION=CELLCONTAININGENDDATE,CELLCONTAININGENDDATE,CHOSENFUNCTION)
  IF the function you choose is equal to the value in your chosen cell then it will return the value of said cell if not, it will keep calculating.
  thanks,
  thom
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  ebeckin · 6 years ago
  
  Hi Thomh or any other experts,
  
  I would like to be able to calculate an age (rounded down to years only) that is the difference between a particular date (the date a patient was referred) and their birthdate so that I end up with the age they were when they were referred by their GP. The date of birth is in field C2 and the referral date is in field D2. I'm not very good at Excel so I'm being specific about this so I can just copy/paste your formula. I've tried using FLOOR and ROUNDDOWN but I'm obviously doing something wrong. I can get a value like 7.8856 by entering =(g2-c2)/365 but if I remove the digital places to one, it rounds it up to 8, which I don't want. Can anybody help me, please? :)
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Thom h · 5 years ago
  
  Not entirely sure of what you're after but I assume you want it to round down thus giving the exact age? In which case you can use rounddown() or I believe floor() will also do the trick.
  Thanks, thom
  
  Reply
To post as a guest, your comment is unpublished.
satheesh · 6 years ago
Why datedif formula not functioning in ms excel 2007?
- Reply
- To post as a guest, your comment is unpublished.
  thomh · 6 years ago
  
  Hi Satheesh,
  It was added around about then so it should work, but you won't get any autosum assistance, you'll have to type it blind. personally, I tend to avoid autosum anyway (once you get into advanced functions it just gets in the way)
  thanks,
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
hk · 7 years ago
Hi, how would I incorportate a text field form into this formula(DOB bookmark referencing the DOB in this formula) into a MSWord 2010 document? I've also tried creating a table in MSWord with 2 cells (DOB in A1 and formula in A2) and I get a syntax error. Here is my formula (with and without the parentheses at the beginning and end gives me the same error). A1 cell contains "7/1/2010":

=(DATEDIF(A1,TODAY(),"Y")& " Years, " &DATEDIF(A1,TODAY(),"YM")& " Months, " &DATEDIF(A1,TODAY(),"MD")& " Days)

Thanks
- Reply
To post as a guest, your comment is unpublished.
aamir khan · 7 years ago
[b]for example if date12/6/1983 i want to calculate the date month year till 1/1/2014 what will be the formula? please help me..[/b]
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 6 years ago
  
  Hi Amir,
  
  This is covered in Method D (Using your first date instead of the DOB and you'll need to take the today() functions out of method D and replace them with your end date, or a cell reference for the cell containing the end date, so if in your example you have 12/06/1983 in call A1 and 1/1/2014 in cell B1 then in cell C1 you could put:
  
  =DATEDIF(A1,B1,"Y") & " Years, " & DATEDIF(A1,B1,"YM") & " Months, " & DATEDIF(A1,B1,"MD") & " Days"
  
  Don't forget that if you have a list and you drag this function down then the cell references will auto update as you pull them down i.e A1 will become A2 then A3..
  
  If you have lots of start date but the end date will always be the value of call B1 then you can lock that cell reference (Make it absolute) but using Dollar signs so instead of writing B1 you'd put $B$1
  
  Thanks,
  
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
sanoj xavier · 7 years ago
how do I convert 19610821 to age?
- Reply
- To post as a guest, your comment is unpublished.
  thomh · 6 years ago
  
  Hi Sanoj,
  you'll need to convert it into a correctly formatted date first. then you can use any of the above functions. I'd recommend either doing this with a function in the column next to it, then referencing that column in your chosen function.
  If you're feeling confident enough in excel you could try making a long argument by putting the conversion function into the age calculation function.
  
  here's an example of what you could use, assuming "19610821" is in cell A1
  
  =DATE(LEFT(A1,4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2))
  
  let me know how you get on.
  
  regards,
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
Sandeep Sanjeevi · 7 years ago
Hi,

This is very helpful and very clear to understand for any one.Helped me a lot.

Thanks,
Sandeep Sanjeevi.
- Reply
To post as a guest, your comment is unpublished.
Lucy · 7 years ago
Hi,
May I know why when I try get for the next age, its become error?

28/06/1986 04/06/2014 27
"28/03/1987 04/06/2014 #VALUE!
"03/03/1942 04/06/2014 #VALUE!
"01/01/2000 04/06/2014 #VALUE!
"03/03/1945 04/06/2014 #VALUE!
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 6 years ago
  
  Hi Lucy,
  
  I'd need more context as to what you're trying to set up, how it's done and your chosen function but it looks as though you've put the age function into the third column then you're dragging it down? If you check each function and just make sure the correct cells are referenced this should do the trick.
  
  #VALUE errors tend to mean that you're using referencing cells that don't contain data in the format the function expects. In this case the functions are looking for dates so Excel may well have converted your dates into text format? Easy fix = highlight the date cells, right click, format cells, format as date.
  
  If that doesn't work you'll need to create a secondary column next to each one (Which you can later hide) then use the =date() function to convert them into date format (see my reply to sanoj xavier further down the page for info on this function).
  
  Thanks,
  
  Thom
  
  Reply
To post as a guest, your comment is unpublished.
VRB · 7 years ago
i need a formula based on a child's birthdate how old he will be on September 1, 2014 (or any other year). cannot finagle the formula =DATEDIF(A13,TODAY(),"Y") to not be TODAY, but 9/1/14. can someone help. I'm sure it's easy :)
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 7 years ago
  
  Hi VRB,
  
  You could either put the date in a separate cell and reference that Cell instead of TODAY(). This would mean you can change the date as you please without having to risk breaking the Argument (Function). Assuming the Date (01/09/14) is in Cell A1, you could use =DATEDIF(A13,TEXT(A1,"DD/MM/YYYY"),"Y")
  
  Alternatively, if you want to just switch the TODAY() function with that date then you'd use this: =DATEDIF(A13,DATE(2014,9,1),"Y")
  
  Reply
To post as a guest, your comment is unpublished.
Jodi · 7 years ago
Hi, How do you use Index and Match function to find age from a person birth date? Thanks.
- Reply
To post as a guest, your comment is unpublished.
Dennison · 7 years ago
Does Method D compensate for the day lost/gained on a leap year?
- Reply
To post as a guest, your comment is unpublished.
mallikarjuna swamy · 7 years ago
04/05/2012
=DATEDIF(A13,TODAY(),"Y") & " Years, " & DATEDIF(A13,TODAY(),"YM") & " Months, " & DATEDIF(A13,TODAY(),"MD") & " Days"

i WANT TO CALCULATE DATE AS OF TODAY BUT WHEN I INSERTED THE FORMULA IN THE CELL I AM GETTING

114 Years, 4 Months, 10 Days WHICH IS OBVIOUSLY WRONG
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 7 years ago
  
  Is the first date definitely in Cell A13 as that's where the Argument is looking for the first date. It looks as though A13 is blank, thus it's counting the difference between today and the start of Excel's Calendar (What it would consider as date 0).
  
  The only other thing it could be is that it's reading the first date as blank because it's not in the correct format, but I believe that would bring up a #Value error. Either way, it's worth checking the format of the Cell. You can either go right click> format Cells> Date> OK, or you can switch each reference to Cell A13 with an =TEXT() so it would be:
  =DATEDIF(TEXT(A13,"DD/MM/YYYY"),TODAY(),"Y") & " Years, " & DATEDIF(TEXT(A13,"DD/MM/YYYY"),TODAY(),"YM") & " Months, " & DATEDIF(TEXT(A13,"DD/MM/YYYY"),TODAY(),"MD") & " Days"
  
  Before all of that though, did you copy and paste your Function straight from your Spread sheet, because I error checked it and there were a couple of spaces in the TODAY() functions that threw it and gave me a #NAME error. Try it again by copying and pasting the following:
  
  =DATEDIF(A13,TODAY(),"Y") & " Years, " & DATEDIF(A13,TODAY(),"YM") & " Months, " & DATEDIF(A13,TODAY(),"MD") & " Days"
  
  Once again, make sure your start date is in Cell A13!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  guru · 6 years ago
  
  Hi,,,
  
  you only looking this only ya,,, :-)
  
  Reply
To post as a guest, your comment is unpublished.
Meak Kanann · 7 years ago
I really love this website. I can find whatever I need immediately. Plz continue to update n search for more useful function. :P
- Reply
To post as a guest, your comment is unpublished.
mark04 · 7 years ago
method D is returning a value while date cell is blank, how to return value "0" or blank when there's no date filled in cell?
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 7 years ago
  
  Hi Mark04,
  
  Excel will automatically treat the Empty Cell as a date and just use the first date in it's Calendar (01/01/1900). You'll need to use an IF() function.
  
  Assuming the start date is in A1 and the end date is in B1 I'd go for =IF(AND(A1>0,B1>0),[b]INSERT CHOSEN DATE FUNCTION HERE[/b],"")
  This will leave the Cell Blank if there isn't a value in both Cells A1 & B1.
  
  Reply
- To post as a guest, your comment is unpublished.
  Thom H · 7 years ago
  
  Hi Mark
  
  Excel uses an internal calendar which starts at 01/01/1900. When a start date isn't present it will automatically choose the first date in its calendari I'd say use an IF() function, assuming the start date is in cell A1, you could use =IF(A1="","",INSERT CHOSEN DATE FUNCTION HERE)
  
  This function checks cell A1, then returns no value if the cell is blank, but does your chosen formula if the cell is active (contains data).
  
  If it's likely that sometimes the start date will be blank and sometimes the end date will, then you'll need to add in the AND( ) function so once again assuming start date is in Cell A1 and end is in B1 you'd use =IF(AND(A1>0,B1>0),INSERT CHOSEN DATE FUNCTION HERE,"")
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  JENNIEJEN · 4 years ago
  
  THOM H...
  thank you so much... you're the best!
  
  Reply
To post as a guest, your comment is unpublished.
Daini Malhotra · 7 years ago
=DATEDIF(A13,TODAY(),"Y") & " Years, " & DATEDIF(A13,TODAY(),"YM") & " Months, " & DATEDIF(A13,TODAY(),"MD") & " Days" this formula is very easy & suitable for calculating age in excel, i like it..............
- Reply
- To post as a guest, your comment is unpublished.
  Rasel · 5 years ago
  
  a problem i faced. like: birth date = 1st Nov.'2014, today is 31st Dec.'2015 then the result comes = 1 year, 1 month, 30 days, but the days should be 1day more i.e. 31 days. how can I do this in the above formula? pls help
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Mubeen · 4 years ago
  
  yes its very helpful for me. i easily understand. :)
  
  Reply
To post as a guest, your comment is unpublished.
Daini Malhotra · 7 years ago
i like method d for calculating age, it's very easy & suitable formula in excel.
:D
- Reply
To post as a guest, your comment is unpublished.
jayson · 7 years ago
i have a whole bunch of cells i have to convert to age, how do i do it faster?
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 7 years ago
  
  create the chosen formula in the cell to the right of the first DOB, then you can drag the function down all of the rows with the anchor in the bottom right of the cell containing the function.
  
  This method will work for most of the above, but if you're using method a. i.e. One where the current date is a reference to a different cell that contains the current date, you'll need to amend the top formula to make it into an absolute reference, so if your formula was option one (=(B2-A2)/365) assuming your dates are all in column A, you can amend the too function to =($B$2-A2)/365. Notice how I've anchored the b2 cell reference while the a2 cell reference will change as you drag the function down.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  KASSEM · 7 years ago
  
  hi i tried all of those methods and it didnt work out it alwyas return with result (#value!)
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Thom H · 7 years ago
  
  Hi Kassem,
  
  #Value means that the Cells your Function refers to are not in the correct format for the Formula you are using i.e. using =SUM() on two Cells containing Text. All of the above functions are written assuming we're working with Cells containing only Dates.
  
  The trouble is, Excel can sometimes see something as a different format to that which you see. Firstly, make sure the dates are in the correct uniform (DD/MM/YYYY). Secondly, make a small amendment to the Cell references in the Date Formula using the =text() function to tell it to read it as a Date .
  
  Assuming we're using the DATEDIF() function with the Birthdate in A1 and the current date in B1, which would be =DATEDIF(A1,B1,"y"), we can force the Function to read Cell A1 as a date by amending it like so =DATEDIF(TEXT(A1,"DD/MM/YYYY"),B1,"y").
  
  Reply
To post as a guest, your comment is unpublished.
fawadkhan · 7 years ago
hello this is fawad khan want some information about ageses
- Reply
To post as a guest, your comment is unpublished.
SDJohnson · 7 years ago
I have been looking for a formula that gives me the age from a Bdate for months now, I found several but they never worked. This was so easy to understand...... Made me look for other items. :-) Thank you so much.
- Reply
To post as a guest, your comment is unpublished.
kaushalendra · 7 years ago
it is very helpful. It is helpful to beginners as well as skilled person
- Reply
To post as a guest, your comment is unpublished.
Roger · 7 years ago
Try this formula to convert date of birht to Age.
- Reply
To post as a guest, your comment is unpublished.
MICH · 7 years ago
very nice..A13 means A2..Very helpful..makes work easier..thanks for the formula..
- Reply
To post as a guest, your comment is unpublished.
Amit sharma · 7 years ago
This is very helpful and very clear to understand for any one. I also need it. It help me a lot.
- Reply
To post as a guest, your comment is unpublished.
S Dorn · 7 years ago
I need a formula that will allow me to punch in a DOB and then find out the exact day that a person turns exactly 70 1/2.
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 7 years ago
  
  =EDATE(A1,906) with DOB in Cell A1
  
  Reply
To post as a guest, your comment is unpublished.
Robyn · 7 years ago
I've been looking for the "round down" option, but didn't know there was one. Thank you!
- Reply
- To post as a guest, your comment is unpublished.
  Thom H · 7 years ago
  
  =FLOOR() would also do the trick
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  she · 6 years ago
  
  [quote name="Thom H"]=FLOOR() would also do the trick[/quote]
  how is this being used? () what should be inside this parentheses?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Thom H · 6 years ago
  
  Hi Robyn,
  
  The Cell you wish to round down should be inside of the parentheses i.e. if you are rounding down cell A1 it would be =Floor(A1)
  
  Reply
To post as a guest, your comment is unpublished.
Berjang Pun · 7 years ago
:lol: it is helpful for me
- Reply
- To post as a guest, your comment is unpublished.
  AMIT · 6 years ago
  
  [quote name="Berjang Pun"]:lol: it is helpful for me[/quot
  VERY EASY TO KNOW THE DATE OF BIRT COUNT AS YEARLY AGE..?
  YOU CAN ..AS BELOW
  10/8/1980-TODAY DATE=?????? THEN YOU CAN / BY 365 EASILY U WILL GETS THE POINT..
  
  Reply

Մուտք Ձեր հաշիվը

Գաղտնաբառ վերագործարկում