Converting to 24 Hour time format
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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