#1
|
|||
|
|||
![]() I thought i would ask this in a new topic, i have imported data that displays like this.
Race 1 - 12:25PM Michael Stewart Plate (1100 METRES) i am using this code to try and force it to 24 hour time. =TEXT(TRIM(CLEAN(MID(LEFT(Q14,FIND("M",Q14)+1),FIND("-",Q14)+1,10))),"hh:mm:ss") But having no luck, any ideas would be helpful.
__________________
One Drive "If the corporates are treating you poorly , just go elsewhere." "If they need you , they will soon find out." "If you need them , you will soon find out." --moeee _______________________________________________ |
#2
|
|||
|
|||
![]() There has to be an easier way of doing this (surely?!) but:
Code:
It's particularly lengthy so that it can handle X:XX and XX:XX format times. Tested with 9:30AM, 10:30AM, 1:30PM and 10:30PM, with the results you would expect. There are undoubtedly better ways, the two necessary points are: 1 - the format needs to be "[H]:mm" for 24 hour time 2 - the string you send it needs to have a space between the time figures and the AM/PM for it to correctly recognise it Last edited by walkermac : 30th January 2016 at 03:48 PM. |
#3
|
|||
|
|||
![]() Thanks, and yes there is.
=TEXT(REPLACE(LEFT(Q14,FIND("M",Q14)-2),1,FIND("-",Q14)+1,"")+0.5*(MID(Q14,FIND("M",Q14)-1,1)="P"),"hh:mm") Your's was a huge effort, i am not even going to try and understand what all that does,lol I am, only good with less complex formulas.
__________________
One Drive "If the corporates are treating you poorly , just go elsewhere." "If they need you , they will soon find out." "If you need them , you will soon find out." --moeee _______________________________________________ Last edited by Shaun : 30th January 2016 at 04:28 PM. |
#4
|
|||
|
|||
![]() How about text to columns, using delimiters, and find replace AM and then PM with a blank.
Then format that column special h:mm.
__________________
RaceCensus - powerful system testing software. Now with over 405,000 Metropolitan, Provincial and Country races! http://www.propun.com.au/horse_raci...ng_systems.html *RaceCensus now updated to 31/07/2024 Video overview of RaceCensus here: http://www.youtube.com/watch?v=W821YP_b0Pg |
![]() |
Thread Tools | Search this Thread |
Display Modes | |
|
|