Remove T and Z from DateTime, and reformat date/time column

nichadam

I used bridge to populate a "time Completed" column at the time a status changes to Resolved, by placing the time modified into this clolumn. However, that's giving me a datetime formatted like: "2024-12-10T19:02:08Z"

I'm trying to reformat this datetime to align with the default created datetime: "12/09/24 11:34 AM".

Can I do this within the bridge workflow? Or can I do this with a new formula column?

Paul Newcome

You would use something like this:

=MID([Time Completed]@row, 6, 2) + "/" + MID([Time Completed]@row, 9, 2) + "/" + MID([Time Completed]@row, 3, 2) + " " + (VALUE(MID([Time Completed]@row, 12, 2)) - IF(VALUE(MID([Time Completed]@row, 12, 2)) >= 12, 12, 0)) + MID([Time Completed]@row, 15, 2) + IF(VALUE(MID([Time Completed]@row, 12, 2)) >= 12, "PM", "AM")

Paul Newcome

You would use something like this:

=MID([Time Completed]@row, 6, 2) + "/" + MID([Time Completed]@row, 9, 2) + "/" + MID([Time Completed]@row, 3, 2) + " " + (VALUE(MID([Time Completed]@row, 12, 2)) - IF(VALUE(MID([Time Completed]@row, 12, 2)) >= 12, 12, 0)) + MID([Time Completed]@row, 15, 2) + IF(VALUE(MID([Time Completed]@row, 12, 2)) >= 12, "PM", "AM")

nichadam

@Paul Newcome wow, I just plugged that in and it worked. I only had to place the colon between the hour and minute and a space between the time and PM. Thank you Paul!

Follow up, this is UTC. Is there a way to add to this to make it PST?

Paul Newcome

Happy to help. 👍️

Glad you were able to get the colon and space sorted. I was so focused on getting the actual data, I forgot to add those pieces in.

Give this a go to subtract 7 hours…

=(DATE(VALUE(LEFT([Time Completed]@row, 4)), VALUE(MID([Time Completed]@row, 6, 2)), VALUE(MID([Time Completed]@row, 9, 2))) - IF(VALUE(MID([Time Completed]@row, 12, 2)) < 7, 1, 0)) + " " + ((IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 + IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 < 0, 24, 0) - IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 + IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 < 0, 24, 0) >= 12, 12, 0) = 0, 12, VALUE(MID([Time Completed]@row, 12, 2)) - 7 + IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 < 0, 24, 0) - IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 + IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 < 0, 24, 0) >= 12, 12, 0))) + ":" + MID([Time Completed]@row, 15, 2) + IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 + IF(VALUE(MID([Time Completed]@row, 12, 2)) - 7 < 0, 24, 0) < 12, " AM", " PM"))