• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding Ages

Hi,


Please refer the below link and minus the dates it will work.


http://chandoo.org/wp/2010/03/23/text-to-date-convertion/


Thanks,

SK
 
Hi Daleep..


Assume A1 = 18/05/1998

and B1 = 15/02/2013


then in C1 use formula as

Code: 
=TEXT(B1-A1,"yy ""Years"" mm ""Months"" dd ""Days""")


Hope it helps..


Regards,

Deb
 
Dear Daleepsingh

Type

A1=18/05/1998

B1=15/02/2013

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


Regards,


Nazmul Muneer
 
@Deb & @Nazmul,


I've used your formulas on how old I am, birth date to today and I'm one month younger using Nazmul's formula. You've got the right one there Nazmul!
 
Hi OldChippy!


Its really nice to see you yonger day by day.. :)


But.. DATEDIF is undocumented..


Please check below post..

http://chandoo.org/forums/topic/excel-2007


Please let us know.. if you grown up or not.. :)


Regards,

Deb
 
Hi Deb,


Thanks for the link to that post. It makes me think now does my old employer owe me an extra one months pension :), I hope so, but i'm still you at heart at least :)
 
@Debraj Roy

Hi!

My radar caught DATEDIF and immediately turned on red alerts... until I saw it was you with "that" link :)

Regards!


@oldchippy

Hi!

50% for the post author, 25% for the link poster, 25% for the employee... you.

Regards!
 
Hi, daleepsingh!

Am I excesively asleep or you may just do at C1:

=A1-B1

formatted as:

yy "years" mm "months" dd "days"

Regards!
 
Good day daleepsingh


You could use :-


= DAYS360( start_date, end_date, [method] )


If you have your dates in cells use the cell reference
 
Good dayt oldchippy


It is a well know fact the SirJB7 has been know to age many with some of his posts :)
 
Good day nazmul_muneer


Both myself and SirJB7 are among the oldies of the forum, SirJB7 is just trying to make some members feel the same way we do :)
 
Hi, b(ut)ob(ut)hc!

Good afternoon, old dog... should I have to remember you that I'm just a little puppy? More indeed if compared with very old dogs...

Regards!

PS: I'm sixteen, so beautiful and I'm mine (thanks Ringo)


@oldchippy!

You're right, it doesn't work, let me investigate why... I hope to not find out anything alike DATEDIF with "m" parameter.

Regards!
 
@Oldchippy

Hi!

I thing that's an issue related to the first Excel day, not actually the equivalent to numeric 1 (01/Jan/1900) but to 0 (00/Jan/1900, which it works, try entering in a cell 01/01/1900 and in another =<that cell>-1).

The year problem, the excess of 1900 years was artificially solved by the editing mask (yy instead of yyyy); the month problem isn't solved (that's why you get 1 month more); there're no day problems as day for date 0 is 0.

Regards!


EDIT:


Assuming starting date in A1 and ending date in B1, these formulas won't add any month or day (don't know year) to your five centuries:

Years: =SI(O(MES(B1)<MES(A$1);Y(MES(B1)=MES(A$1);DIA(B1)<DIA(A$1)));-1;0)+AÑO(B1)-AÑO(A$1) -----> in english: =IF(OR(MONTH(B1)<MONTH(A$1),AND(MONTH(B1)=MONTH(A$1),DAY(B1)<DAY(A$1))),-1,0)+YEAR(B1)-YEAR(A$1)

Months: =SI(O(MES(B1)<MES(A$1);Y(MES(B1)=MES(A$1);DIA(B1)<DIA(A$1)));12;0)+SI(DIA(B1)<DIA(A$1);-1;0)+MES(B1)-MES(A$1) -----> in english: =IF(OR(MONTH(B1)<MONTH(A$1),AND(MONTH(B1)=MONTH(A$1),DAY(B1)<DAY(A$1))),12,0)+IF(DAY(B1)<DAY(A$1),-1,0)+MONTH(B1)-MONTH(A$1)

Days: =SI(DIA(B1)<DIA(A$1);DIA(FIN.MES(B1;-1));0)+DIA(B1)-DIA(A$1) -----> in english: =IF(DAY(B1)<DAY(A$1),DAY(EOMONTH(B1,-1)),0)+DAY(B1)-DAY(A$1)
 
@SirJB7,


I know I'm old but I don't go as far back as 1900. :-0


My birthday is on 19th July, so if you use today (15/04/2013) and 19/07/2012, using


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


I get 8 months 27 days since my last birthday, which is the correct answer, if I use


=TEXT(B1-A1,"yy ""Years"" mm ""Months"" dd ""Days""")


I get 9 months 26 days


So although there maybe issues with DATEDIF, it works for me


Regards OC
 
@oldchippy

Hi!

Didn't see your last post and just edited mine previous. Give a look to the formulas, I don't like them, but I can't find any simpler.

Regards!

PS: BTW, would you be as kind as stop using the zombie function DATEDIF?
 
@SirJB7,


Well done my friend, a monster of a formula.


For those who want it in one cell with the text as well, here is the complete formula


=IF(OR(MONTH(B1)<MONTH(A$1),AND(MONTH(B1)=MONTH(A$1),DAY(B1)<DAY(A$1))),-1,0)+YEAR(B1)-YEAR(A$1)&" years "&IF(OR(MONTH(B1)<MONTH(A$1),AND(MONTH(B1)=MONTH(A$1),DAY(B1)<DAY(A$1))),12,0)+IF(DAY(B1)<DAY(A$1),-1,0)+MONTH(B1)-MONTH(A$1)&" months "&IF(DAY(B1)<DAY(A$1),DAY(EOMONTH(B1,-1)),0)+DAY(B1)-DAY(A$1)&" days"
 
@olchippy!

Glad you solved it, now you can get sure of what are old are you. Dumb of me who thought that Excel only worked with Gregorian calendar and dates from the 19th century or greater... it's amazing that it works with Julian too.

Regards!

PS: thanks of course for your nice words too.

PS: what I'm yet no sure if that formula will work for b(ut)ob(ut)hc, as I haven't checked it with prior 46 B.C. dates (BTW, they're not from the Julian calendar, are they from the solar one?)
 
@ SirJB7,


If b(ut)ob(ut)hc requires a formula prior to 1900, then he may like to check this one out


http://excelribbon.tips.net/T012277_Tombstone_Date_Math.html


Whether it will work as far back as 46 B.C. who knows?
 
@oldchippy

Hi!

You're not gonna think that I "intended" to say that your birthday enters in the Julian calendar era...

You're not gonna think that I "intended" to say that b(ut)ob(ut)hc's one enters in the ancient Egipcian solar calendar era...

Nothing further from my thoughts... I guess I should improve my written English :p

Regards!
 
You must log in or register to reply here.
Back
Top