Converting to 24 Hour time format

[Blondek]

Morning all, 

A bit of a sticky one here - I've got a string field I'd like to convert to Date in 24 hour format. 

The data looks like this: 

I've started by using the DateTime Parse tool, which looks as though it has worked fine, now I need to write a formula that will take the AM/PM field and convert the Time into 24 hours accordingly. 

Can anyone please help?

Thanks

[Jonathan-Sherman]

Hi @Blondek,

Do you want to combine the data and time into a datetime value (with time in 24 hour format) or leave date and time separate (as they currently are, just with time in 24 hour format?)

Regards,

Jonathan

[Blondek]

Hi @Jonathan-Sherman 

I'd like to create leave the date and time separate columns, but convert the Time in 24 hour format, so that 02:30:32 PM is simply 14:30:32.

Thanks in advance, 

Matt

[Jonathan-Sherman]

Hi @Blondek,

This formula should achieve that:

IF [AM/PM] = 'PM'
THEN DateTimeAdd('1970-01-01 ' + [Time of Usage], 12, 'hours')
ELSE [Time of Usage]
ENDIF

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[Blondek]

Hi @Jonathan-Sherman 

Thanks - almost there! After I pop this formula in, I get this: 

I wonder if its the DateTimeAdd that doesnt work with this one...

Thanks, 

Matt

[Jonathan-Sherman · Accepted solution]

Hi @Blondek,

Could you check your data type is a Time and not a DateTime? It's likely that's the issue. If it isn't you may need to use a select tool to change it to a Time data type.

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[Blondek]

Ah, @Jonathan-Sherman that's the problem! I had it in DateTime. 

Legend! Thank you! 

[ayush_jajodia]

Hi guys,

i saw this page and found it useful. However i observed an anomaly with this text and the solution is:

IF [AM/PM] = 'PM' && Left([Time of Usage],2)!='12'

THEN DateTimeAdd('1970-01-01 ' + [Time of Usage], 12, 'hours')
ELSE [Time of Usage]
ENDIF

if you have a time, lets say, 12:43pm, you don't wanna convert it to 00:43 as it is already PM. the above addition sorts this out.

[saltysnax]

Hi, just a thought, with the && Left([Time of Usage],2)!='12' part of IF [AM/PM] = 'PM' && Left([Time of Usage],2)!='12'

Wouldn't that make midday 2400, and midnight 1200 because midday = 12pm and midnight is 12am?

[ankur13agrawal]

What is time is 12:36:00 PM,it will still add 12 hours to it and I guess which is not require

and also what if it is 12:30:00 AM