=QUOTIENT(A7 - Q7;365.25)
=FLOOR((A7-Q7)/365.25;1)
FJCC wrote:Either of these should work
- Code: Select all Expand viewCollapse view
=QUOTIENT(A7 - Q7;365.25)
or
- Code: Select all Expand viewCollapse view
=FLOOR((A7-Q7)/365.25;1)
=years(q7;A7;1)
Skompy wrote:FJCC wrote:Either of these should work
- Code: Select all Expand viewCollapse view
=QUOTIENT(A7 - Q7;365.25)
or
- Code: Select all Expand viewCollapse view
=FLOOR((A7-Q7)/365.25;1)
Thanks!
Skompy wrote: =QUOTIENT(A7 - Q7;365.25)
or
=FLOOR((A7-Q7)/365.25;1)
I have just found some examples when the DOB and the race date are on the same day, the above formula does not show the right age in years. Once the age goes onto the next day (e.g. DOB = 8 March and race date = 9 March), the age in years shows correctly.
Do you know why that might be?
RusselB wrote:Just a suggestion for an alternative formula.
- Code: Select all Expand viewCollapse view
=years(q7;A7;1)
gerard24 wrote:Skompy wrote: =QUOTIENT(A7 - Q7;365.25)
or
=FLOOR((A7-Q7)/365.25;1)
I have just found some examples when the DOB and the race date are on the same day, the above formula does not show the right age in years. Once the age goes onto the next day (e.g. DOB = 8 March and race date = 9 March), the age in years shows correctly.
Do you know why that might be?
Because 1 year isn't 365.25 days exactly.
Use YEARS function as Russel suggest.RusselB wrote:Just a suggestion for an alternative formula.
- Code: Select all Expand viewCollapse view
=years(q7;A7;1)
RoryOF wrote:I thought all horses were deemed to have the same birthday - 01 January in Northern Hemisphere, 01 August in Southern Hemisphere?
=DATEDIF(DateOfRace; DateOfBirth; "y")
=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
Lupp wrote:In recent LibO there is (for compatibility reasons) a function DATEDIF() for the purpose. (Yes the name has this ridiculous single "F".)
The age in years for the racing horse is computed then asHaving the dates in Q7 and A7 respectively again, we can also use the rather long but logically clear formula
- Code: Select all Expand viewCollapse view
=DATEDIF(DateOfRace; DateOfBirth; "y")
- Code: Select all Expand viewCollapse view
=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
I would be much interested in examples resulting in errors.Skompy wrote:The second formula you have provided works on all but a small number of cells, which are dates that are within 1-2 days of each other.
Lupp wrote:I would be much interested in examples resulting in errors.Skompy wrote:The second formula you have provided works on all but a small number of cells, which are dates that are within 1-2 days of each other.
=YEARS(Q7;A7;0)
=ROUND(YEARS(Q7;A7;1) + DAYS(DATE(YEAR(A7);MONTH(A7);DAY(A7));DATE(YEAR(A7);MONTH(Q7);DAY(Q7)))/DAYSINYEAR(A7);3)
keme wrote:I believe you need the YEARS() function, but with the last (third) parameter given as zero, to correctly return actual age in full years from birth to race date. Using 1 there, as previously suggested, will return the number of new-year (Dec-Jan) transitions passed between the two dates.
So, to show the age in number of years (integer), replace your formula withThis will correctly take leap years into account, instead of spreading the leap day evenly across 4 years. (Also, the rule is that century transitions must be divisible by 400 to give a leap year, so the exact factor is slightly smaller: 365.2425)
- Code: Select all Expand viewCollapse view
=YEARS(Q7;A7;0)
If you need "true age" with 3 decimals, use "new-year count" instead (third parameter 1 as previously suggested), adjust with the the distance in days between birthday and race date (using current year for both dates), and round to 3 decimals. This will also keep the leap days in place, avoiding the "spread error".Distance between race and birth date may be positive (race after birthday) or negative (race before birthday).
- Code: Select all Expand viewCollapse view
=ROUND(YEARS(Q7;A7;1) + DAYS(DATE(YEAR(A7);MONTH(A7);DAY(A7));DATE(YEAR(A7);MONTH(Q7);DAY(Q7)))/DAYSINYEAR(A7);3)
Skompy wrote:[...]
So do I enter your ROUND formula and your DAYS formula all in one cell (as I have done) and which seems to have worked and resolved all the above issues?
[...]
Skompy wrote:[...]
I also have retained this formula that Lupp provided, to show age in only years in the cell next to the one showing true age.
=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
[...]
Lupp wrote:What's the idea of using a "year" defined in an arbitrary way as the unit for something then called "true age"?
The common year is inappropriate as a unit. It is a calendaric conception. If you want to compare ages at a higher resolution than ordinary years allow for, you should use days (as spreadsheets implicitly do). If you try to get even beyond that you get lost with any conception based on calendaric terms. You need to run a stopwatch then. "Days" and "hours" as used in everyday life are ruled by legal norms little interested in technical consistency. Under the rule of haphazard regulations ("daylight saving time" e.g.) days differ in length and hours may be skipped or doubled... And if a parliament decides to apply to a different time zone your horse may get younger.
It's close to what you did anyway:Skompy wrote:How would you do things differently to give me what I need?
=A7-Q7
Lupp wrote:It's close to what you did anyway:Skompy wrote:How would you do things differently to give me what I need?That's the age in days disregarding the TOD of birth and of the race as well. Its the division by 365.25 what screws things up (a bit). If you compare these results to the "full-year-age" in cases where this is exact, you get unavoidably some irritating/unplausible results for the reason I already mentioned: The year of some odd days+fraction is not a unit. You may use it like a unit (defining the LY - https://en.wikipedia.org/wiki/Light-year e.g.) as long as you keep it decoupled from the calendar then. That's not an option for you, however, since you have to handle calendaric dates for DOB, DOR.
- Code: Select all Expand viewCollapse view
=A7-Q7
In addition you treat horses a bit like stars as you use the Julian year like astronomers do instead of either the Gregorian year or a year defined astronomically in one or another way.
Strange: To find a significant number (around 10) of cases where the difference gets striking you need to go through about 11000 pairs of random dates as statistics tells me. Did you?
keme wrote:The "days count" provides a perfectly linear time scale. The calendar approach where you use "year" as the unit, makes a slightly "bumpy line". The leap year bumps and monthly dents are so small that they make little difference for our everyday tasks. The calendar is the time scale we share, worldwide, and for many tasks it is the only viable choice.
When you do statistical work on a population of this magnitude, however, those tiny bumps and dents in time may become significant. In this case you may need to decide whether it is the exact age (day count) or relation to calendar/seasonal variations - e.g. using "years + days", or "day_count/365.25" - which is most important.
"Day_count/365.25" is also linear, but will not always return integer results when you need it to (which is the reason why you started this thread, it seems). From this you get the "bumps" you first mentioned, in the classification of age groups.
Users browsing this forum: No registered users and 15 guests