Why does this formula not subract 12 from a part of a string ?

Hello

I am still trying to wrap my head around the date aspects of Alteryx and attempting to change a military time to a standard 12 hour time, but to no luck, I understand there are other ways around this but I do not understand why this way in particular does not work, any help would be appreciated.

First formula - this ensures there are four digits in the field

if Length([MILITARY BEGIN_TIME]) < 4
then "0" + [MILITARY BEGIN_TIME]
else [MILITARY BEGIN_TIME]

Endif

Second formula - attempt to replace any first two digits that are greater than 12 - this part does not convert, although it does run with errors

if (ToNumber(left([MILITARY BEGIN_TIME], 2))) > 12
then ((tonumber(Left([MILITARY BEGIN_TIME], 2))) - 12) + ToNumber(right([MILITARY BEGIN_TIME],6))
else ([MILITARY BEGIN_TIME])
endif

Thank you

Help

Accepted answers

ImadZidan

Hello @rossid ,

try this formula. I have tried on 21:00 and the output showed 9:00

if (ToNumber(left([mt], 2))) > 12
then ToString(((tonumber(Left([mt], 2))) - 12)) +':'+ ToString((right([mt],2)))
else ([mt])
endif

I notice ToNumber(right([MILITARY BEGIN_TIME],6)), why 6? may be this is where yo are going wrong.

Let me know if any issues.

Example on using the datetime tool is included in the workflow if you want to use it.

mtTime.yxmd

All comments

ImadZidan

Hello @rossid ,

try this formula. I have tried on 21:00 and the output showed 9:00

if (ToNumber(left([mt], 2))) > 12
then ToString(((tonumber(Left([mt], 2))) - 12)) +':'+ ToString((right([mt],2)))
else ([mt])
endif

I notice ToNumber(right([MILITARY BEGIN_TIME],6)), why 6? may be this is where yo are going wrong.

Let me know if any issues.

Example on using the datetime tool is included in the workflow if you want to use it.

mtTime.yxmd

rossid

Thank You it worked !

Clearly just me being silly using tonumber instead of tostring, and I think 6 was a mis type,

thanks again

Goppi

there are multiple reasons this didn't work:

1) you convert rightly the hours into a number to be able to substract 12 hours - however as it's now a number the operator + that is supposed to concat the hour with the minutes acts now as an addition. So, once the 12 hours are substracted, you need to convert the number back to a string - like this

tostring(tonumber(Left([MILITARY BEGIN_TIME], 2))-12)

2) you convert the minutes to a number as well. I assume you did this because Alteryx complained that you can use the operator + with a number and string. But once you have changed the hours as above, just leave the conversion of the minutes ot a number away and you have a string + string operation. Additionally, I guess 6 should really be 2 if the military tiem is like "2100".

right([MILITARY BEGIN_TIME],6)

3) If I understand military time correct, it doesn't have a punctuation, but you probably want one with standard 12 hour time and the hour without a leading zero in case it's less than 10. That works easily now that both parts are string, but

The second formula is in total like this:

if (ToNumber(left([MILITARY BEGIN_TIME], 2))) > 12 then
  tostring(tonumber(Left([MILITARY BEGIN_TIME], 2))-12)
else 
  tostring(tonumber(Left([MILITARY BEGIN_TIME], 2)))
endif
+ ":" + right([MILITARY BEGIN_TIME],2)

4) just as a side-info. As you need to convert the hour into a number anyway, you could easily replace both formulas with a single formula like this - it's only working without conversion errors if the military time doesn't have a colon:

if ToNumber([MILITARY BEGIN_TIME]) >= 1300 then 
  tostring(tonumber(Left([MILITARY BEGIN_TIME], 2))-12)
else 
  Left([MILITARY BEGIN_TIME], 1)
endif
 + ":" + right([MILITARY BEGIN_TIME],2)

5) Having said that, another way by using regex to give you more flexiblity as it can deal with military times such as 2100, 800, 21:00, 08:00, etc and iif instead of if then else in a two step could be this

1st formula for field hour(int)

ToNumber(REGEX_Replace([MILITARY BEGIN_TIME], "^(\d{1,2}):?(\d{2})$", "$1"))

2nd formula

ToString(iif([hour]>=13, [hour]-12, [hour]))
+":"
+Right([MILITARY BEGIN_TIME],2)

The Regex expression reads like this:

^        Start of string (anchor)
(        begin capturing group #1
  \d{1,2}one or two digits
)        end capturing group
:?       optional colon
(        begin capturing group #2
  \d{2}  two digits
)        end group
$        end of string anchor

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703