# Age calculations with only 112 days in a year

1. ## Age calculations with only 112 days in a year

I'm not new to excel. I know all the basic stuff, but I need some help for this one. I hope this is the right place for this thread.

I'm playing an online manager game (Hattrick). A season/year in hattrick is only 112 days (and not 365ish).

Example: 19 years and 1 day < 19 years and 111 days < 19 years and 112 = 20 years and 0 days < 20 years and 1 day.
*Notice that 19 years and 112 days are equal to 20 years and 0 days.

I want excel to calculate the number of days a player has been in my club. From the day I bought him to the days he's sold. Example: I buy a player when he's 19 years and 34 days. He is sold again when he is 20 years and 66 days. That's 144 days or 20 weeks and 4 days.

So if A1 contains his age at buy-in and B1 contains his age when he's sold, what do I have to type in C1?

If that's not possible I could also type his age in years (at buy-in) in A1, age in days in B1, his age in years (selling) in C1, his age in days in D1, and the results in E1 and F1 (years and days).

Cheers
European_Idiot  Register To Reply

2. ## Re: Age calculations with only 112 days in a year

So if A1 contains his age at buy-in
Exactly what are the contents of A1 if he's 19 years 34 days?  Register To Reply

3. ## Re: Age calculations with only 112 days in a year Originally Posted by shg Exactly what are the contents of A1 if he's 19 years 34 days?
Well, It depends on what's easiest to make. I would prefer 19,34 or 19.34, but a formula like that is very hard to create, so I could just settle with 2 cells (total 6 cells) with A1: 19, B1: 34 and so on...  Register To Reply

4. ## Re: Age calculations with only 112 days in a year

I've done something like this before. But that was a formula which updated the players age, for every day that went by.

=((ROUNDDOWN(((TODAY()-A1+112,0)/112,0);0,0)))+B1
A1 = next birthday (date)
B1 = age in years

If I put this in C1, the output would be his age, and when 112 days had gone by, the value would increase by 1.

This is what I used for days (in the following cell):
=(((TODAY()-B1+112,0)/112,0)-(ROUNDDOWN(((TODAY()-B1+112,0)/112,0);0,0)))*112,0
Where B1 is the next birthday as well. The output from this formula would increase by 1 every time the clock passed midnight. When it reached 112, it would start over from 0.

I've asked the guy who gave me this one, but he doesn't know exactly.

But I hope it'll help....  Register To Reply

5. ## Re: Age calculations with only 112 days in a year

=ROUND(((INT(B1)+MOD(B1,1)/0.112)-(INT(A1)+MOD(A1,1)/0.112))*112,0)

it rounds because I was getting odd numbers due to not being able to store decimals acurately

enter the ages in years as years.days IE 5.001 5 years 1 day 5.100 5 years 100 days  Register To Reply

6. ## Re: Age calculations with only 112 days in a year Originally Posted by squiggler47 =ROUND(((INT(B1)+MOD(B1,1)/0.112)-(INT(A1)+MOD(A1,1)/0.112))*112,0)

it rounds because I was getting odd numbers due to not being able to store decimals accurately

enter the ages in years as years.days IE 5.001 5 years 1 day 5.100 5 years 100 days
Thanks for the reply, but I'm having some probs with it.

It wont accept the ,1 of B1 and A1, and it wont accept 0.112 as well.

And could you also tell me the full names of the functions (MOD, INT)? My excel is in Danish, and I need the danish names so it can 'read' it.  Register To Reply

7. ## Re: Age calculations with only 112 days in a year

ok if you have the danish version of excel, you will need to change the , to ; and the 0.112 to 0,112

as for the functions try those changes first!  Register To Reply

8. ## Re: Age calculations with only 112 days in a year

aparently :-

ROUND = AFRUND
MOD = REST
INT = HELTAL

here is a reference for you !

Danish to English Excel reference!  Register To Reply

9. ## Re: Age calculations with only 112 days in a year

Two more possibilities

=(INT(B1)-INT(A1))*112+(MOD(B1,1)-MOD(A1,1))*1000

or

=(DOLLARDE(B1,112)-DOLLARDE(A1,112))*112

DOLLARDE function is an inbuilt function in Excel 2007 but in earlier versions it's part of Analysis ToolPak add-in  Register To Reply

10. ## Re: Age calculations with only 112 days in a year Originally Posted by squiggler47 ok if you have the danish version of excel, you will need to change the , to ; and the 0.112 to 0,112

as for the functions try those changes first!
Okay, once again thank you.

It comes out with a number now, but far from the right one.

Here's what I've writen in the cells:
A1:
17.001
B1:
17.045
C1:
=AFRUND(((HELTAL(B1)+REST(B1;1)/0,112)-(HELTAL(A1)+REST(A1;1)/0,112))*112;0)

The output is 4928, and it should have been something like 44 or 044.  Register To Reply

11. ## Re: Age calculations with only 112 days in a year Originally Posted by daddylonglegs Two more possibilities

=(INT(B1)-INT(A1))*112+(MOD(B1,1)-MOD(A1,1))*1000

or

=(DOLLARDE(B1,112)-DOLLARDE(A1,112))*112

DOLLARDE function is an inbuilt function in Excel 2007 but in earlier versions it's part of Analysis ToolPak add-in
Your formula comes with the same output as the other: 4928

This is what I've done:
=(HELTAL(B2)-HELTAL(A2))*112+(REST(B2;1)-REST(A2;1))*1000

Notice: I have used the cells below for this one..  Register To Reply

12. ## Re: Age calculations with only 112 days in a year

I'm not sure how you get that figure, the result I get is 44 as expected, see attached example where I've used both formulas that I suggested  Register To Reply

13. ## Re: Age calculations with only 112 days in a year Originally Posted by daddylonglegs I'm not sure how you get that figure, the result I get is 44 as expected, see attached example where I've used both formulas that I suggested
I found the error. The input in A1 and B1 was 17.001 when it should have been 17,001.

Thanks again all for the help. This is awesome   Register To Reply

14. ## Re: Age calculations with only 112 days in a year

What about if I want to calculate the age adding number of days passed to first age?

For example: I have the starting age of 17.001; 150 days was passed from that date (1 year and 38 days), so the result age must be 18.039. How to calculate it inverting the formula to calculate days between two ages?

thank you  Register To Reply

15. ## Re: Age calculations with only 112 days in a year Originally Posted by alecs81 What about if I want to calculate the age adding number of days passed to first age?

For example: I have the starting age of 17.001; 150 days was passed from that date (1 year and 38 days), so the result age must be 18.039. How to calculate it inverting the formula to calculate days between two ages?

thank you
Hey Im new here. But the last question there I really want to know the answer of my self.
Apart from that. What a wonderful thread. Helped me tons on my challenge.  Register To Reply

16. ## Re: Age calculations with only 112 days in a year Originally Posted by Perrrry Hey Im new here. But the last question there I really want to know the answer of my self.
Apart from that. What a wonderful thread. Helped me tons on my challenge.
take a look here: http://www.excelforum.com/excel-new-...-112-days.html
I'm just curious, what do you need it for?? hattrick?  Register To Reply

17. ## Re: Age calculations with only 112 days in a year

Hattrick yes. Thank you.  Register To Reply

18. ## Re: Age calculations with only 112 days in a year Originally Posted by Perrrry Hattrick yes. Thank you.
i did something to manage youth team, but I abandoned project then... it was too hard for me to import ht xml in excel file....   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 