General Betting
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
sort by:
Show
per page
Replies: 14
|
By:
You pasted the wrong response Grey Shot.
|
|
By:
=date(TRUNC(A1/10000), TRUNC(MOD(A1,10000)/100), MOD(A1,100))
assuming the number is in cell A1 |
|
By:
There you go Grey Shot. That's the formula I gave in horse racing in excel speak.
|
|
By:
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. |
|
By:
be careful adding 1900... I belive excel adds tha 1900???
|
|
By:
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.
|
|
By:
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. |
|
By:
Cheers Feck, I think im there .
Let me know if you need any Tables from this year ;O) |
|
By:
Thanks a lot everyone much appreciated
|
|
By:
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? |
|
By:
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! |
|
By:
Thanks ILBM. What would you do though if you were inputting dates of artefacts then?
|
|
By:
this one Grey imo
:-) |
|
By:
Woooo yes yes yes cheers mate
|