Calculate birthday and age as if there were 112 days in a year

Hi,

I have Excel 2003,

I'm having trouble with a formula of calculating age from a specific date.

But first I'd like to know a formula to get to a birthdate, using the same 112 days in a year.

I have this:

=DATE(YEAR(O3)/112;MONTH(O3);DAY(O3))

but it shows me back absurd dates, and I need this to be accurate, since I need this cell to get to this, which I'm not sure if it is well built as well:

=INT((TODAY()-N3)/112) &" years and "& DAY(HOJE()-N3) &" days"

O3= Next birthday
N3= Birthdate

Can anyone help?

I don't understand how you want to use a year that is 112 days but still use month and day from a year that is 365 days. So I don't understand what you are trying to do with your first formula.

For the second formula, if a "year" is 112 days, and you need a formula for the years and days from a given date, it would be

Formula:

=INT((TODAY()-N3)/112) &" years and "& MOD(TODAY()-N3,112) &" days"

(By the way thank you for the correct use of the subjunctive mood in your thread title. )

Reply to all, what I want to do is get to get a birth date assuming that on the next birthday the player (this is for Hattrick, an online football management game) will be 17 years old.

The only thing thing that comes to my mind is something like this:

=DATE(YEAR((O3/112)-17);MONTH(O3);DAY(O3))

But of course I'm aware this is wrong, just trying to know where.

My bad on the insufficient data.

Originally Posted by Deep_Shadow

.....But first I'd like to know a formula to get to a birthdate, using the same 112 days in a year.....

Surely you need more information than just the date of the next birthday to get birthdate, you'll need the age (in 112 day years) too, won't you? Assuming Next birthday in O3 and age (on that date) in O2 try this formula to get birthdate in N3

=O3-O2*112

That will then be consistent with Jeff's suggested formula for age, e.g. if O2 is 42, for example, then Jeff's formula will give you "41 years and xx days"

Hi,

It would assist greatly if you could produce examples which shows dates and the resultant birthdays.

Assuming, therefore, that you are simply counting back 17*112 days from O3 then that would just be this formula as per my suggestion in previous post

=O3-112*17

If that doesn't work for you then please supply some examples as Richard suggests, e,g, if 17th birthday is 25th December 2014 what date do you expect to get as birthdate?

You need to use 6StringJazzer's suggested formula, i.e.

Originally Posted by 6StringJazzer

=INT((TODAY()-N3)/112) &" years and "& MOD(TODAY()-N3,112) &" days"

In your version you are using DIA/DAY - that function gives you day of the month so never gives a value > 31

Yes, I've been using HOJE, since my version is portuguese, but the error message strickly points to the -N3,112

I think it can't be a "," (comma) there.

Edit: If I shift it to ;112, it shows me #NAME?, as if it has a an invalid name error...

Is the comma supposed to be * (multiplication)? Cause if it is, it also shows me that error...

Yes, I think you need a semi-colon separator instead of the comma, i.e. in Portuguese

=INT((HOJE()-N3)/112) &" years and "& MOD(HOJE()-N3;112) &" days"

See attached