Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Smile Calculate horses age as at 1 August

I need a worksheet that will allow me to enter a date (for the horse show) and then my horses birthday to give me the horses' show age.
In Australia all horses age a year on 1st August so if the horse was born on Feb 2nd, 2012 on 1st August 2012 it will be one year old (even though technically it is only 5 months old it has to show as a yearling.

Ideas ??
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 829
Default Calculate horses age as at 1 August

"minigg" wrote:
In Australia all horses age a year on 1st August so if the horse
was born on Feb 2nd, 2012 on 1st August 2012 it will be one year
old


Incredible!

So what would a horse's age be on 1 Aug 2012 if it was born in July 2012: 1
year old?!

And what would a horse's age be on 31 July 2013 if it was born in Sep 2012:
0 years old?!

Looking at http://www.melbournetrackreport.com/...australia.html, I
do see that it says: "Australian horses have their official birthday on
August 1st".

But it does not say __which__ Aug 1st. I can imagine that the date of birth
is the "closest" Aug 1st, with a rule to handle exactly between two firsts
of Aug in leap years.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Calculate horses age as at 1 August

On Tue, 26 Feb 2013 22:36:48 +0000, minigg wrote:


I need a worksheet that will allow me to enter a date (for the horse
show) and then my horses birthday to give me the horses' show age.
In Australia all horses age a year on 1st August so if the horse was
born on Feb 2nd, 2012 on 1st August 2012 it will be one year old (even
though technically it is only 5 months old it has to show as a
yearling.

Ideas ??


With the DOB in A1, try:

=DATEDIF(DATE(YEAR(A1)-1+(MONTH(A1)7),8,1),TODAY(),"y")

This assumes that a horse born on 8/1/2012 will not be one year old until 8/1/2013. If that is not the case, and a horse born on 8/1/2012 is immediately one year old, then try:

=DATEDIF(DATE(YEAR(A1)-1+(MONTH(A1-1)7),8,1),TODAY(),"y")

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Calculate horses age as at 1 August

On Tue, 26 Feb 2013 17:57:22 -0800, "joeu2004" wrote:

But it does not say __which__ Aug 1st. I can imagine that the date of birth
is the "closest" Aug 1st, with a rule to handle exactly between two firsts
of Aug in leap years.


All the reading I've done indicates that it is the "next" Aug 1. In other words, a race horse born on 31 Jul 2012 would be 1 year old on 1 Aug 2012 (and compete as a one year old)

In the US the "birthday" is Jan 1.
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Calculate horses age as at 1 August

On Tue, 26 Feb 2013 17:57:22 -0800, "joeu2004" wrote:

But it does not say __which__ Aug 1st. I can imagine that the date of birth
is the "closest" Aug 1st, with a rule to handle exactly between two firsts
of Aug in leap years.


For the American Jockey Club -- http://www.jockeyclub.com/pdfs/rules_12_final.pdf

Age of the Thoroughbred: For the purpose of determining
age, the date of birth for all Thoroughbreds is deemed
to be January 1 of the year of foaling.

So, at least in the US, a horse born on Dec 31, 2011 would have been "born" on Jan 1, 2011.

Hmmm, wonder if there's a similar set of rules for the Australian Jockey club:



  #6   Report Post  
Junior Member
 
Posts: 2
Smile

Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
On Tue, 26 Feb 2013 22:36:48 +0000, minigg wrote:


I need a worksheet that will allow me to enter a date (for the horse
show) and then my horses birthday to give me the horses' show age.
In Australia all horses age a year on 1st August so if the horse was
born on Feb 2nd, 2012 on 1st August 2012 it will be one year old (even
though technically it is only 5 months old it has to show as a
yearling.

Ideas ??


With the DOB in A1, try:

=DATEDIF(DATE(YEAR(A1)-1+(MONTH(A1)7),8,1),TODAY(),"y")

This assumes that a horse born on 8/1/2012 will not be one year old until 8/1/2013. If that is not the case, and a horse born on 8/1/2012 is immediately one year old, then try:

=DATEDIF(DATE(YEAR(A1)-1+(MONTH(A1-1)7),8,1),TODAY(),"y")
Thanks Ron - your solution is perfect !

Yes breeding seasons between Australia (1 Aug) and the USA are different (1 Jan) - stands to reason given that Spring is in different months
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Calculate horses age as at 1 August

On Wed, 27 Feb 2013 22:57:10 +0000, minigg wrote:

Thanks Ron - your solution is perfect !


Glad to help. Thanks for the feedback.


Yes breeding seasons between Australia (1 Aug) and the USA are different
(1 Jan) - stands to reason given that Spring is in different months



Well, I would opine that a system that has horses of both one day and 365 days old being treated as if they were the same age defies reason, but then, I'm not a horse breeder or racer :-)
However, since, unlike humans, horses doen't breed year round, the age differences between 1 year olds might not be that great.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
is there a formula for "Week of August 4-8' Teacher_Becky New Users to Excel 3 August 2nd 08 12:58 AM
changing August 31, 2007 into 31.08.2007 ziom Excel Worksheet Functions 4 September 17th 07 06:44 AM
date field is held as 38932 how do I export as 3rd August 06 Dave D Excel Programming 2 March 15th 07 01:02 PM
VBA, How to open a file with date like 29 August 2004 Grek[_18_] Excel Programming 2 August 29th 04 08:05 PM
Additional feature to code given on August 6th Metallo[_3_] Excel Programming 3 August 20th 04 10:31 AM


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"