parse out and convert 24 hour time (1 Viewer)

[mreniff]

I need to extract out the time, convert to time in 24 hour format and then use it to sort a report by time. The time needs to sort chronological order. Not in number value order. 1 pm (etc) comes after 12 noon on.

• Using this expression:
  • Realtime: CStr(Format(CDate(Left([Start Time],2) & ":" & Right([Start Time],2)),"h:nn AM/PM"))
• Original data: 10:45 a.m., 101 Oregon Hall
  • results are: 10:45 AM
• Original data; 12:15 p.m., 101 Oregon Hall
  • results are correct; 12:15 PM
• Original data: 1:45 p.m., 101 Oregon Hall
  • results are #ERROR

 

[pbaldy]

I would probably use CDate(), the Left() function along with the InStr() function to get everything before the comma. You may need Replace() to get rid of the periods too.

 

[pbaldy]

I should have added that you won't get proper sorting with CStr(). With a string, 10:00am comes before 8:00am.

 

[MajP]

Public Function GetTime(strTIme As String) As Date
  GetTime = CDate(Replace(Split(strTIme, ",")(0), ".", ""))
End Function


Public Sub TestGet()
 Dim x As String
 x = "10:45 a.m., 101 Oregon Hall"
 Debug.Print GetTime(x)
 x = "10:45 P.m., 101 Oregon Hall"
 Debug.Print GetTime(x)
End Sub

This works for me.

 

[mreniff]

How do I convert 1:45 to 13:45 using this function?


Public Function GetTime(strTIme As String) As Date
GetTime = TimeSerial(Split(strTIme, ":")(0), Split(strTIme, ":")(1), 0) End Function

Pass in 1:45 and get 1:45 A.M
Pass in 13:45 and get 1:45 p.m
work with real dates and times not a string.
***************

 

[theDBguy]

Hi. Didn't you say you want to convert the time into 24-hour format? So, 1:45 p.m. should be 13:45, correct?

Edit: Oops, too slow...


In any case, you could try it this way:


Format(Replace(Left([Start Time],InStr([Start Time],",")-1),".",""), "Short Time")


Hope it helps...

 

[MajP]

What are you really passing in. DBguy and I assume it is what you say
"10:45 a.m., 101 Oregon Hall", 12:15 p.m., 101 Oregon Hall
I deleted the first post and provided a second. It is going to return a REAL date. you can format a real date for display but you sort on the real date.

 

[theDBguy]

What are you really passing in. DBguy and I assume it is what you say
"10:45 a.m., 101 Oregon Hall", 12:15 p.m., 101 Oregon Hall
I deleted the first post and provided a second. It is going to return a REAL date. you can format a real date for display but you sort on the real date.

Hi. As you know, Format() will return a Variant String. But since we're using 24-hour format, then 1:00 pm (13:00) will (should) still sort after 12:00 pm (12:00).

 

[mreniff]

I get error message The expression you entered contains invalid syntax. YOu may have entered an operand without an operator at ".".


Format(Replace(Left([Start Time],InStr([Start Time],",")-1)".",""), "Short Time")

 

[MajP]

Disregard my first deleted post

Public Function GetTime(strTIme As String) As Date
GetTime = TimeSerial(Split(strTIme, ":")(0), Split(strTIme, ":")(1), 0) End Function

I deleted and reposted in 4. The second post handles both cases
"10:45 a.m., 101 Oregon Hall"
"10:45 P.m., 101 Oregon Hall
and will return a real date 10:45 AM and 10:45 PM (or 22:45, Access does not know the difference since it is the value .947916666666667).
If you want then you can format the results.

 

[theDBguy]

Sure, but it is a much better when working with dates to work with dates. With the provided formula the OP can then do real things with the time.
Based on the users data what you posted does not work
"12:15 p.m., 101 Oregon Hall" will return 12:15 AM since you are not reading the p.m.

Hi. The OP has a choice. I am not sure any date calculation is required. All I know is there's a request to "sort" the data by date. As for whether the expression I posted works or not, let's try to break it down to see where I may be missing something. First, let's say [Start Time] = "12:15 p.m., 101 Oregon Hall"


Then, this:

Format(Replace(Left([Start Time],InStr([Start Time],",")-1),".",""), "Short Time")

breaks down as follows:

Left([Start Time],InStr(Start Time],",")-1)

should result in this:

"12:15 p.m."

Then, the next part:

Replace("12:15 p.m.",".","")

should result in this:

"12:15 pm"

And the last part:

Format("12:15 pm", "Short Time")

should result in:

"12:15"

Did I miss anything?

 

[mreniff]

You are wonderful. I need to take lunch and then I will try this.

 

[theDBguy]

I get error message The expression you entered contains invalid syntax. YOu may have entered an operand without an operator at ".".


Format(Replace(Left([Start Time],InStr([Start Time],",")-1)".",""), "Short Time")

Ah, looks like I was missing a comma. Sorry.

Format(Replace(Left([Start Time],InStr([Start Time],",")-1),".",""), "Short Time")

 

[mreniff]

This problem is solved. I appreciate all of the advise each of you provided me. The final solution is



SortTime: Format(Replace(Left([Apttime],InStr([AptTime],",")-1),".",""),"Short Time")

 

[theDBguy]

This problem is solved. I appreciate all of the advise each of you provided me. The final solution is



SortTime: Format(Replace(Left([Apttime],InStr([AptTime],",")-1),".",""),"Short Time")

Hi. Congratulation! Glad to hear you got it sorted out. We're all happy to help. Good luck with your project.