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

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

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.

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

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"

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

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

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?

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

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))?

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!

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

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!