Removing AM/PM

Ron Erdeljac

Is there a quick easy way to remove AM/PM from column entries. Making 9:00 AM, 9:00

Thanks,

Paul Newcome

Try this...

=SUBSTITUTE(SUBSTITUTE([Time Column]@row, "AM", ""), "PM", "")

Paul Newcome

Try this...

=SUBSTITUTE(SUBSTITUTE([Time Column]@row, "AM", ""), "PM", "")

Ron Erdeljac

Ty Paul !

Ron Erdeljac

@Paul Newcome Paul, I realized the that my data set also has time entries without the space between the "0" and the "a" (9:00am). I tried adapting your suggested formula but cannot seem to remove the am/pm when there is no space used. Do you have a suggestion?

One additional question. I'm using the below formula to determine the difference between my two time fields. It works fine until I cross the 12:00 pm threshold. I get a negative # response.

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60)))

Lastly, It is not completely necessary but I would like to change my digital # response back into hrs:min if possible.

Thanks again for your input.

Paul Newcome

It should work. Maybe it is being case sensitive. Lets try making everything upper case before removing the AM and PM.

=SUBSTITUTE(SUBSTITUTE(UPPER([Time Column]@row), "AM", ""), "PM", "")

Paul Newcome

For the time difference, take a look through the below thread. There are currently 4 pages worth of various time based solutions. There should be one in there that will help get you sorted out.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time