Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert now (current date and time) to a string ddmmyyyyhhnn. | Excel Programming | |||
convert string to date & time | Excel Worksheet Functions | |||
Convert to Military time? | Excel Discussion (Misc queries) | |||
Function to convert Time String to Time | Excel Worksheet Functions | |||
Convert to military time - part II | Excel Programming |