Removing AM/PM
Is there a quick easy way to remove AM/PM from column entries. Making 9:00 AM, 9:00
Thanks,
Best Answer
-
Try this...
=SUBSTITUTE(SUBSTITUTE([Time Column]@row, "AM", ""), "PM", "")
Answers
-
Try this...
=SUBSTITUTE(SUBSTITUTE([Time Column]@row, "AM", ""), "PM", "")
-
Ty Paul !
-
@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.
-
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", "")
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!