+ Reply to Thread
Results 1 to 18 of 18

Age calculations with only 112 days in a year

  1. #1
    Registered User
    Join Date
    05-29-2010
    Location
    Fredericia, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Question 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

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    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?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-29-2010
    Location
    Fredericia, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by shg View Post
    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...
    Last edited by European_Idiot; 05-29-2010 at 08:58 PM. Reason: corrected misspellings.

  4. #4
    Registered User
    Join Date
    05-29-2010
    Location
    Fredericia, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    7

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

  5. #5
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

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


  6. #6
    Registered User
    Join Date
    05-29-2010
    Location
    Fredericia, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by squiggler47 View Post
    =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.

  7. #7
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    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!

  8. #8
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Age calculations with only 112 days in a year

    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!

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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

  10. #10
    Registered User
    Join Date
    05-29-2010
    Location
    Fredericia, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by squiggler47 View Post
    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.

  11. #11
    Registered User
    Join Date
    05-29-2010
    Location
    Fredericia, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by daddylonglegs View Post
    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..

  12. #12
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-29-2010
    Location
    Fredericia, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by daddylonglegs View Post
    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

  14. #14
    Registered User
    Join Date
    11-22-2011
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    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

  15. #15
    Registered User
    Join Date
    11-21-2012
    Location
    Norway
    MS-Off Ver
    Excel 2011
    Posts
    2

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by alecs81 View Post
    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.

  16. #16
    Registered User
    Join Date
    11-22-2011
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by Perrrry View Post
    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?

  17. #17
    Registered User
    Join Date
    11-21-2012
    Location
    Norway
    MS-Off Ver
    Excel 2011
    Posts
    2

    Re: Age calculations with only 112 days in a year

    Hattrick yes. Thank you.

  18. #18
    Registered User
    Join Date
    11-22-2011
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Age calculations with only 112 days in a year

    Quote Originally Posted by Perrrry View Post
    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....

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1