General Betting
Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
Hi I posted this on the Horse racing forum and it was suggested I may get a better response on here. Fingers crossed
Hi within the Computer Timeform Table the dates are expressed as numbers for example
1060527 = 27/05/2006
CAn anyone give me a steer as to how I convert them to dd/mm/yyyy by writing a formula in excel
Also is the timeform format a universal DB format or did they just invent it themselves?
Thanks in advance.
Smar Tarse 23 Mar 11:18
Iam not sure about excel, but if i was doing it in VBasic i would do it something like this.
'==========================================
Public Function convert_date(the_date As String) As String
Dim the_year As String, the_month As String, the_day As String
the_year = Mid$(the_date, 2, 2)
the_month = Mid$(the_date, 4, 2)
the_day = Mid$(the_date, 6, 2)
convert_date = (the_day & "-" & the_month & "-" & the_year)
End Function
Hi within the Computer Timeform Table the dates are expressed as numbers for example
1060527 = 27/05/2006
CAn anyone give me a steer as to how I convert them to dd/mm/yyyy by writing a formula in excel
Also is the timeform format a universal DB format or did they just invent it themselves?
Thanks in advance.
Smar Tarse 23 Mar 11:18
Iam not sure about excel, but if i was doing it in VBasic i would do it something like this.
'==========================================
Public Function convert_date(the_date As String) As String
Dim the_year As String, the_month As String, the_day As String
the_year = Mid$(the_date, 2, 2)
the_month = Mid$(the_date, 4, 2)
the_day = Mid$(the_date, 6, 2)
convert_date = (the_day & "-" & the_month & "-" & the_year)
End Function
Show More
=date(TRUNC(A1/10000), TRUNC(MOD(A1,10000)/100), MOD(A1,100))
assuming the number is in cell A1
assuming the number is in cell A1
There you go Grey Shot. That's the formula I gave in horse racing in excel speak.
I'd change ILBM's formula to
date(TRUNC(A1/10000)+1900, TRUNC(MOD(A1,10000)/100), MOD(A1,100))
so it handled dates beyond 2050 though.
date(TRUNC(A1/10000)+1900, TRUNC(MOD(A1,10000)/100), MOD(A1,100))
so it handled dates beyond 2050 though.
be careful adding 1900... I belive excel adds tha 1900???
How do you mean ILBM? You want it to add 1900. e.g. in the example Grey Shot gave the year would be TRUNC(106527/10000)+1900 = 2006.
excel will add 1900 automatically to the year parameter you pass to the date() function if it's between 0 and 1899 - so 106 becomes 2006 anyway.
TBH, it doesn't matter. if you pass a value 2000, it doesn't add 1900 so it won't make any difference.
TBH, it doesn't matter. if you pass a value 2000, it doesn't add 1900 so it won't make any difference.
Cheers Feck, I think im there .
Let me know if you need any Tables from this year ;O)
Let me know if you need any Tables from this year ;O)
I think ILBM was more deserving of your thanks ILBM.
ILBM, I was thinking if the tf date was 1550101 would excel not assume that the year was 155 unless the 1900 was added? If not, how would you create such a date?
ILBM, I was thinking if the tf date was 1550101 would excel not assume that the year was 155 unless the 1900 was added? If not, how would you create such a date?
Feck, excel's date function checks the parameter passed in for the year. If it's between 0 and 1899 it adds 1900 to it on your behalf before using it to format the date.
So 0 = 1900, 1899=3799. I doubt if anyone will be using the ssheet in the year 3799. :)
Adding 1900 before passing in as you suggested still works ok, because excel doesnt add (because year is now = 1900). This method would carry on working after 3799 too - so it's more futureproof!
So 0 = 1900, 1899=3799. I doubt if anyone will be using the ssheet in the year 3799. :)
Adding 1900 before passing in as you suggested still works ok, because excel doesnt add (because year is now = 1900). This method would carry on working after 3799 too - so it's more futureproof!
Thanks ILBM. What would you do though if you were inputting dates of artefacts then?
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539