Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
Suppose I have the following string variable:
s = "10:20 PM" Is there a function that will convert s to military like so?: s = "2200" ' Now represents military time -Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
"RG III" wrote in message
Suppose I have the following string variable: s = "10:20 PM" Is there a function that will convert s to military like so?: s = "2200" ' Now represents military time -Robert There's no builtin function but easy enough to roll your own. But why not simply number-format the cell(s) with "hhmm" Peter T |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
On Wednesday, September 25, 2019 at 8:31:31 AM UTC-7, Peter T wrote:
There's no builtin function but easy enough to roll your own. But why not simply number-format the cell(s) with "hhmm The data is not stored in a cell. I was just trying to find a method to convert time strings stored in variables. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
Hi,
Am Wed, 25 Sep 2019 11:06:07 -0700 (PDT) schrieb RG III: The data is not stored in a cell. I was just trying to find a method to convert time strings stored in variables. try: s = "10:20 PM" t = TimeValue(s) s = Hour(t) & Minute(t) Regards Claus B. -- Windows10 Office 2016 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
On Wednesday, September 25, 2019 at 11:20:51 AM UTC-7, Claus Busch wrote:
s = "10:20 PM" t = TimeValue(s) s = Hour(t) & Minute(t) Those functions are a big help. The only problem is if the input is something like this: s = "10:04 PM" ' Output == "224" I guess if the minutes value is less than 10, I just need to prefix the minutes with a "0" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
Hi,
Am Wed, 25 Sep 2019 11:37:31 -0700 (PDT) schrieb RG III: The only problem is if the input is something like this: s = "10:04 PM" ' Output == "224" then try: s = "10:04 PM" t = TimeValue(s) s = Format(Hour(t), "00") & Format(Minute(t), "00") Regards Claus B. -- Windows10 Office 2016 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
Hi,
Am Wed, 25 Sep 2019 20:49:25 +0200 schrieb Claus Busch: s = "10:04 PM" t = TimeValue(s) s = Format(Hour(t), "00") & Format(Minute(t), "00") or easier: s = "10:04 PM" s = Format(s, "hhmm") Regards Claus B. -- Windows10 Office 2016 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
On Wednesday, September 25, 2019 at 11:53:08 AM UTC-7, Claus Busch wrote:
or easier: s = "10:04 PM" s = Format(s, "hhmm") Yes! That is much easier than creating my own solution. Thanks again! BTW, what's a quick way to convert hours to minutes? For example, a = "1:26:34" ' Convert this to "86:34" b = "0:01:12" ' Convert this to "1:12" I'm only interested in the minutes and seconds fields. So, if the hours field has a value, then convert that to 60 minutes and add to the total? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
Hi,
Am Wed, 25 Sep 2019 12:02:18 -0700 (PDT) schrieb RG III: BTW, what's a quick way to convert hours to minutes? For example, a = "1:26:34" ' Convert this to "86:34" b = "0:01:12" ' Convert this to "1:12" I'm only interested in the minutes and seconds fields. So, if the hours field has a value, then convert that to 60 minutes and add to the total? Try: a = "1:26:34" varTmp = Split(a, ":") s = varTmp(0) * 60 + varTmp(1) & ":" & varTmp(2) Regards Claus B. -- Windows10 Office 2016 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
On Wednesday, September 25, 2019 at 12:21:52 PM UTC-7, Claus Busch wrote:
Try: a = "1:26:34" varTmp = Split(a, ":") s = varTmp(0) * 60 + varTmp(1) & ":" & varTmp(2) Thanks again Claus. I found one situation that had an odd return value, but for now I'm assuming that the data will always be entered as hh:mm:ss. If a = "0:5:5", it returned "5:5" when it should have been "5:05", but I'm okay with your solution because I expected the input to always be in the correct format. Claus, I have an unrelated question. How do you use the split() function to split up a string full of tab characters? Is it split(sVal, chr(9))? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
On Wednesday, September 25, 2019 at 1:15:14 PM UTC-7, RG III wrote:
Claus, I have an unrelated question. How do you use the split() function to split up a string full of tab characters? Is it split(sVal, chr(9))? Disregard the tab question. The label of "vbTab" represents tab. Duh! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
Hi Robert,
Am Wed, 25 Sep 2019 13:15:06 -0700 (PDT) schrieb RG III: On Wednesday, September 25, 2019 at 12:21:52 PM UTC-7, Claus Busch wrote: Try: a = "1:26:34" varTmp = Split(a, ":") s = varTmp(0) * 60 + varTmp(1) & ":" & varTmp(2) Thanks again Claus. I found one situation that had an odd return value, but for now I'm assuming that the data will always be entered as hh:mm:ss. If a = "0:5:5", it returned "5:5" when it should have been "5:05", but I'm okay with your solution because I expected the input to always be in the correct format. change the line for s to: s = varTmp(0) * 60 + varTmp(1) & ":" & Format(varTmp(2), "00") Regards Claus B. -- Windows10 Office 2016 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA time conversion
On Thursday, September 26, 2019 at 1:10:26 AM UTC-7, Claus Busch wrote:
change the line for s to: s = varTmp(0) * 60 + varTmp(1) & ":" & Format(varTmp(2), "00") Perfection as usual. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Conversion | Excel Worksheet Functions | |||
time conversion | Excel Worksheet Functions | |||
Time Conversion | Excel Discussion (Misc queries) | |||
time conversion | Excel Programming | |||
TIME CONVERSION | Excel Programming |