Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 161
Default Convert string to military time string

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.






  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Convert string to military time string

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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Convert string to military time string

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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 161
Default Convert string to military time string

"GS" wrote:

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.




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Convert string to military time string

"GS" wrote:

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.


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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 161
Default Convert string to military time string

"GS" wrote:

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.



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Convert string to military time string

"GS" wrote:

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.


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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default Convert string to military time string

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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Convert string to military time string

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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 161
Default Convert string to military time string

"pb" wrote:

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".






  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default Convert string to military time string

On Saturday, January 25, 2014 6:05:16 PM UTC-6, Robert Crandal wrote:
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|p m){0,1}|^(\d{1,2}).*?:.*?(\d{1,2}).*?(am|a m|pm|p m)$|^(\d{1,2}).*?:.*?(\d{1,2})$/i
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default Convert string to military time string

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.")?
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default Convert string to military time string

On Friday, January 31, 2014 9:37:00 AM UTC-6, pb wrote:
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!)
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default Convert string to military time string

On Friday, January 31, 2014 9:37:00 AM UTC-6, pb wrote:
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 and I'll send you my regx wb with sloppy time entry udf. Subject line should read SLOPPYTIME or it will be trashed.
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default Convert string to military time string

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert now (current date and time) to a string ddmmyyyyhhnn. David Langschied Excel Programming 2 November 5th 08 01:34 PM
convert string to date & time [email protected] Excel Worksheet Functions 4 October 30th 06 05:26 PM
Convert to Military time? telewats Excel Discussion (Misc queries) 2 March 13th 06 07:17 PM
Function to convert Time String to Time Andibevan Excel Worksheet Functions 6 August 19th 05 01:19 PM
Convert to military time - part II Carole O Excel Programming 6 August 14th 04 01:55 AM


All times are GMT +1. The time now is 08:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"