Convert string to military time string

[Robert Crandal]

I would like to hear everyone's techniques for converting
a string into military time format ONLY if the string resembles
a standard time format.

For example,

"12:00am" --> "0000"
"8:45 pm" --> "1945"
"1:22 PM" --> "1322"
"3 : 36 p m --> "1546"
"2:00" --> "0200" [special case]
"hello" --> "0000" [special case]

In the special cases where a user forgets to
type "am", "AM", "pm", or "PM", I will want to
covert as shown above (morning military time).

If the string does not resemble a standard time
format, then we can just convert that to "0000" hours.

 

[GS]

Try...

NumberFormat = "hmm;@"

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Oops! Didn't include for leading zero! Format s/b...

NumberFormat = "hhmm;@"

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Robert Crandal]

NumberFormat = "hhmm;@"

This was working great until I typed in military times
in the cell. If you type "1300" or any other military
time it gets converted to "0000". If a user types "1300",
then the result should have been "1300".

Is it possible to create a solution that does not rely on
cell formatting? I will probably read string data from
a userform field or a string variable.

 

[GS]

This was working great until I typed in military times
in the cell. If you type "1300" or any other military
time it gets converted to "0000". If a user types "1300",
then the result should have been "1300".

Is it possible to create a solution that does not rely on
cell formatting? I will probably read string data from
a userform field or a string variable.

You could dev a macro that queries the user input and returns the
desired value conditionally, as per your criteria. Perhaps a Select
Case construct? This, then, would allow evaluating user input before
populating cells!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Robert Crandal]

You could dev a macro that queries the user input and returns the desired
value conditionally, as per your criteria. Perhaps a Select Case
construct? This, then, would allow evaluating user input before populating
cells!

Ya, that seems simple enough. I will also try to see if a solution
with regular expressions is faster or more convenient. thanks GS.

 

[GS]

Ya, that seems simple enough. I will also try to see if a solution
with regular expressions is faster or more convenient. thanks GS.

Perhaps Ron will chime in with a RegEx solution. Best wishes
otherwise...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[pb]

In your original post you just wanted something to convert a string. In follow-up postings it gets changed to a user input field. I ask because it you are convert existing text strings a second question comes to mind. Whathappens if the string includes seconds (ex: "01:23:45", "01:23:45pm")? What is to be done with them (displayed, ignored, round to the nearest minute)?
-pb

 

[GS]

In your original post you just wanted something to convert a string.

In follow-up postings it gets changed to a user input field. I ask
because it you are convert existing text strings a second question
comes to mind. What happens if the string includes seconds (ex:
"01:23:45", "01:23:45pm")? What is to be done with them (displayed,
ignored, round to the nearest minute)? -pb

Good point but, AFAIK, *military time* doesn't count seconds as does
24hr time. But then, I could be wrong!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Robert Crandal]

In your original post you just wanted something to convert a string. In
follow-up postings it gets changed to a user input field. I ask
because it you are convert existing text strings a second question comes
to mind. What happens if the string includes seconds (ex:
"01:23:45", "01:23:45pm")? What is to be done with them (displayed,
ignored, round to the nearest minute)?
-pb

My users are not expected to enter seconds, such as "01:23:45" or
"01:23:45pm".
However, if someone does enter seconds, I would like to ignore the
trailing seconds.

So, "01:23:45pm" should be converted to "1323".

 

[oldyork90]

I would like to hear everyone's techniques for converting

a string into military time format ONLY if the string resembles

a standard time format.



For example,



"12:00am" --> "0000"

"8:45 pm" --> "1945"

"1:22 PM" --> "1322"

"3 : 36 p m --> "1546"

"2:00" --> "0200" [special case]

"hello" --> "0000" [special case]



In the special cases where a user forgets to

type "am", "AM", "pm", or "PM", I will want to

covert as shown above (morning military time).



If the string does not resemble a standard time

format, then we can just convert that to "0000" hours.

sorry.. just had to try
for the strings you gave...
@a = ("12:00am",
"8:45 pm",
"1:22 PM",
"3 : 36 p m",
"2:00",
"hello",
"01:23:45",
"01:23:45pm");

/^(\d{1,2}).*?:.*?(\d{1,2}).*?:.*?(\d{1,2}).*?(am|pm){0,1}|^(\d{1,2}).*?:.*?(\d{1,2}).*?(am|a m|pm|p m)$|^(\d{1,2}).*?:.*?(\d{1,2})$/i

 

[pb]

Not quite sure how to implement that into a spreadsheet or VBA, but it sure looks impressive.

Will it handle the punctuation on the meridian (ex: "a.m.", "P.M.")?

 

[oldyork90]

Not quite sure how to implement that into a spreadsheet or VBA, but it sure looks impressive.



Will it handle the punctuation on the meridian (ex: "a.m.", "P.M.")?

Hahahah. I don't know how it works in Excel... but I'll mess about. The above was a perl exercise. I use regex in Excel but I don't understand exactly how they assign matches.

And this is a "one liner". I'm not sure you'd want to do it this way. It sure wouldn't be easy to maintain.

No, it doesn't handle the periods in the am/pm. It wasn't it in the strings of possibles... but I'll add it. Get back. (But I'll bet someone else will trump me. There are some very talented guys in here!)

 

[oldyork90]

Not quite sure how to implement that into a spreadsheet or VBA, but it sure looks impressive.



Will it handle the punctuation on the meridian (ex: "a.m.", "P.M.")?

pb - send an email address to this junk account (e-mail address removed) and I'll send you my regx wb with sloppy time entry udf. Subject line should read SLOPPYTIME or it will be trashed.

 

[pb]

I have never seen/used Perl. It reminds me of a command line from the early days of Vi.

I asked about the punctuation on the meridian because like earthquakes and users, it is not "if" it will happen but "when".

The email for the workbook is on it's way. Thank you.

-pb