Using DATEDIF, to produce a negative Y/M/D

[FlyinIron406]

Hi All,

I'm trying to make an excel to keep track of those in my family and birthdays. I'm also trying for each member, to compare it to my birthday and have it display in Years Months Days the difference. Works great for those younger than me using this formula:

Please Login or Register  to view this content.

Whereas F2 is my birthday and F4, F5, F6 etc are my family members' birthdays.


My issue is when they are older than me. I want it to display a negative years etc: - 1 year 9 months 2 days as an example.

It's just now showing a #NUM! error.

Is there a way to correct this or make it work in the way I want? Or is another formula needed?

Thanks so much.

[Carim]

Hi,

You could test following :

Please Login or Register  to view this content.

[FlyinIron406]

Hi,

You could test following :

Please Login or Register  to view this content.

I will give it a shot and report back. Thank you!

[FlyinIron406]

Carim,

That really seemed to work! Thanks!

One question in regards to the way it displays. I have them as you can see displaying as: - 1 yrs 0 m 1 d *and* 3 yrs 2 m 26 d.

If I got to sort column, it seems to sort right to a point. However a -1 and a -10 and a 1 and 10 sort to the same area. Is there anyway to fix the display to be essentially:

XX yrs XX m XX d, to where maybe it'd sort correctly?

Thanks again so much!

[Carim]

Glad this is helping you out ...

Regarding the sort issue, not sure to fully understand ... but would recommend to sort the input data (and not the column with your formula)

[davsth]

Your answers as excel does not display negative dates, are being returned as a text string, as such 1s and 10s will be together before 2s. If you just created another column that was one date taken away from the other and formatted as a number. this would give you the number of days, sorting by this would probably put the data in the order you desire. If it is just the difference use ABS. this would also probably work just by sorting by the other persons DOB

$F$2-F4

or

ABS($F$2-F4) for the absolute difference

[FlyinIron406]

Sorting the Birthdate indeed did work! Brainfart there missing that.

As for the display of the difference's, I was wondering if there was a way to force it to display as such:

## yrs, ## m, ## d

Thanks for all your help!!!

[FlyinIron406]

You're right. As for now this works great for what I need, minus maybe the thing I replied saying I wish I could get the DATED if to display as such:

## yrs, ## m, ## d

As for what Carim said, and you. Indeed, my issue can easily be done by just sorry the birthday's column.

Thanks so much for your reply!

[davsth]

if you mean everything is 2 digits, altering each bit to put it inside a text function forcing 2 digits may be what you want


TEXT(DATEDIF($F$2,F4,"y"),"00") &" Yrs" etc etc etc

[FlyinIron406]

That works great! Thanks Davsth!

Was curious if there's a way to make the years show Positive (+) in front. I see how the ( - ) is implemented, but not sure where to put the " + ".

[davsth]

something like, and there are probably better ways

if($F$2>F4,"+","") & the rest of your formula as it currently exists


is that what you mean?

[FlyinIron406]

something like, and there are probably better ways

if($F$2>F4,"+","") & the rest of your formula as it currently exists


is that what you mean?

I’ll give That a shot and reply back!

[FlyinIron406]

something like, and there are probably better ways

if($F$2>F4,"+","") & the rest of your formula as it currently exists


is that what you mean?

Hey Davsth,

I tried that as you suggested and I get "too many arguments for this function". Here's the whole formula and maybe that'll help.

Please Login or Register  to view this content.

[davsth]

Please Login or Register  to view this content.

or am I missing something, you don't even need the if