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
Last edited by European_Idiot; 05-30-2010 at 01:06 PM. Reason: Solved
Exactly what are the contents of A1 if he's 19 years 34 days?So if A1 contains his age at buy-in
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
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...Originally Posted by shg
Last edited by European_Idiot; 05-29-2010 at 08:58 PM. Reason: corrected misspellings.
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....
=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
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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.
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!
Regards
Darren
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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!
Regards
Darren
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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
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.
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..
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks