• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to convert a string, which has both numbers and text, to a time?

N

NJ786

New Member
we get scanned documents and the files does not contain standard formatting for the fields, although text only or number only fields have no issue but lately got stuck on this one think. After converting the time field which is in the format "12.30am" & "1.30pm" we get something like "1200am" & "130pm" now the problem starts when we try to get this string to be converted to a time format as "12:00:00 AM" & "1:30:00 PM" some how the formula is not helping in getting the right results.


"=TEXT((LEFT(G2,LEN(G2)-2)),"00:00")+((RIGHT(G2,2)="pm")/2)"


Where G2 has the "1200am" & "130pm"


G...........H

-------------------

0000am 12:00:00 AM (1200am converted to 0000am for easy recognition)

0030am 12:30:00 AM

100am 1:00:00 AM

130am 1:30:00 AM

1130am 11:30:00 AM

1200pm 12:00:00 AM (problem starts when it shows 12pm instead of the 00am)

1030pm 10:30:00 PM

100pm 1:00:00 PM

130pm 1:30:00 PM

0000am 12:00:00 AM

0030am 12:30:00 AM

100am 1:00:00 AM

1230pm 12:30:00 AM

100pm 1:00:00 PM


can someone solve this issue or recommend a better formula method to be used?
 
Before i get confused, what's the original data look like and what format do you want? You have already showed 2 columns of data. were they both source data format? Please kindly be clear. thanks.
 
Assuming you just have the single column of times, and the 2nd column was that you wanted (or what your formula was giving)

This formula:

=TIMEVALUE(LEFT(A1,FIND("m",A1)-4)&":"&MID(A1,FIND("m",A1)-3,2)&IF(ISNUMBER(SEARCH("a",A1))," AM"," PM"))


With this cell format, if you're wanting 12:00 AM to show up as 00:00 AM:

h:mm:ss AM/PM;;[hh]:mm:ss" AM"


should do the trick.
 
I like your solution Luke M. it's neat. mine was just a simple


=IF(LEN($A:$A)=6,LEFT($A:$A,2),LEFT($A:$A,1))&":"&IF(LEN($A:$A)=6,MID($A:$A,3,2),MID($A:$A,2,2))&IF(RIGHT($A:$A,2)="AM"," A.M."," P.M.")
 
In the same sheet, i am also trying to get the correct date. Issues faced are, one that i have to enter a date manually when ever new data is available in the first cell (column C) and the next cell calculates the date according to the condition set in the column B. Secondly i am using a condition based on the Time we derived from the above scenario to know if the date should change based on the Time at/after midnight.


A1=Time, B1=Condition to determine Date change, C1=Change date based on value of B


B>> "=IF(A2="","False1",IF(I4=A2,"True1",IF(A2=0,"AfterMID",IF((A4>=A3)+(A3<A2)=2,"Midnight","False"))))"


C>> "=IF(B3="Midnight",C2+1,C2)"


A...............B.........C..........

-------------------------------------

9:00:00 PM False 07/11/2011

10:00:00 PM False 07/11/2011

10:30:00 PM False 07/11/2011

11:00:00 PM False 07/11/2011

11:30:00 PM False 07/11/2011

12:00:00 AM Midnight 08/11/2011 (Date Changed based on the condition in B)

12:30:00 AM AfterMID 08/11/2011

1:00:00 AM False 08/11/2011

1:30:00 AM False 08/11/2011


If possible can you please fine tune and tidy up the formula as they are a bit confusing and also if the two steps can be merged into one formula to skip a step.


Appreciated.
 
Another issue related to time and date.


In Column A the below data is present, to get the title and time seperated is ok, but getting the time formatted as we did before is a bit of an issue.


19.40 "Axe". - 2 >>

21.00 "fish" >>

22.35 "peacock Feather >>

0.20 "Carton"

0.55 "big fish"

2.15 "hen Feather" >>


What i did was to get the time part out of the cell into another cell at column B as below.


"=TRIM(SUBSTITUTE("0"&LEFT(A3,5),".","",1))"


The "0" is due to the single char and double char before the "." in the text string.


So we get B=020 / B=2100


Now in column C we have the below.


"=IF(LEN(B3)=5,LEFT(B3,3),LEFT(B3,2))&":"&RIGHT(B3,2)"


But then again can't get the date changed in column D with the formula posted previously with conditions to detect the midnight as soon as it detects the first "0" (zero) in C it changes the date as it thinks the midnight has passed due to the condition.


Guys i know i am asking too much but different scans have different formats and i am trying to unify all into one so as to get a proper record at the end.


appreciated.
 
ref topic


http://chandoo.org/forums/topic/date-and-time-issue?replies=11#post-13747


thanks guys
 
You must log in or register to reply here.
Back
Top