Age calculations with only 112 days in a year

**European_Idiot** · 05-29-2010, 07:56 PM

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

**shg** · 05-29-2010, 08:42 PM

So if A1 contains his age at buy-in

Exactly what are the contents of A1 if he's 19 years 34 days?

**European_Idiot** · 05-29-2010, 08:57 PM

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...

**European_Idiot** · 05-29-2010, 09:20 PM

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....

**squiggler47** · 05-30-2010, 03:08 AM

=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

**European_Idiot** · 05-30-2010, 09:49 AM

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.

**squiggler47** · 05-30-2010, 10:34 AM

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!

**squiggler47** · 05-30-2010, 10:37 AM

aparently :-

ROUND = AFRUND
MOD = REST
INT = HELTAL

here is a reference for you !

http://hi.au.dk/uploads/File/kurser/...ion-dk-eng.pdf

Danish to English Excel reference!

**daddylonglegs** · 05-30-2010, 10:49 AM

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

**European_Idiot** · 05-30-2010, 11:17 AM

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.

**European_Idiot** · 05-30-2010, 11:21 AM

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..

**daddylonglegs** · 05-30-2010, 12:36 PM

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

**European_Idiot** · 05-30-2010, 01:05 PM

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

**alecs81** · 11-22-2011, 04:04 PM

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

**Perrrry** · 11-21-2012, 02:27 PM

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.

**alecs81** · 11-21-2012, 02:55 PM

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?

**Perrrry** · 11-21-2012, 02:59 PM

Hattrick yes. Thank you.

**alecs81** · 11-21-2012, 03:03 PM

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....

Age calculations with only 112 days in a year

LinkBack

Thread Tools

Rate This Thread

Display

Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Re: Age calculations with only 112 days in a year

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions