I am trying to convert time to 24H Time

I found a formula from @Bassam Khalil that had me create helper Columns for AM/PM, Hours, Minutes.

The first three formula's appeared to "work" but when I used the forth formula for helper column 24H it was #UNPARSEABLE (Formula used below). I changed "Time on Site" to my Column name.

=IFERROR(LEFT([Time On-Site]@row, 10) + " " + IF([AM PM]@row = "AM", Hours@row + ":" + Minute@row, VALUE(Hours@row) + 12 + ":" + Minute@row), "")


Not sure what I am doing wrong. If there is an easier way please let me know!

Find more posts tagged with

Accepted answers

All comments

Michelle Choate 2

Have you thought about an additional column that is 24HR Time that is equal to the IF statement in your formula? Then you could just use the rest of your formula as IFERROR(LEFT([Time On-Site]@row, 10) + " " + [24HR Time]@row,""). Also I do not believe you need the VALUE in the IF formula it would just be the SUM(Hours@row + 12)

Dreim

@Michelle Choate 2 Thanks for the quick reply. When It comes to formula's in smartsheets I am a rookie to say the least.
I do have a helper Column that is for 24H.

=IFERROR(LEFT([Estimated Time of Return]@row, 10) + " " + IF([AM PM]@row = "AM", Hours@row +
":" + Minute@row, SUM(Hours@row + 12) + ":" + Minute@row), "")

This is the formula I used by taking the "Value" out and adding your SUM(Hours@row + 12).

Still getting an "error". I could have misunderstood you too. Like I mentioned I am a Formula rookie. haha

Michelle Choate 2

What is the error? And yes, I suggested adding another column to put your entire IF statement into. Then your final output column would be the simple addition of two columns together.

Michelle Choate 2

The other help I would suggest is breaking the formula into parts and seeing which part is throwing the error.

Dreim

@Michelle Choate 2 I either get unparsenable or the formula just appears in the column as text.

Michelle Choate 2

Want to jump on a call and troubleshoot? Email me at michelle.choate@outlook.com

Dreim

Thanks for the offer @Michelle Choate 2. I think I took another run at it with some different formula and it appears to be working (for now). Thanks for your help!

Michelle Choate 2

My pleasure @Dreim! Do you want to share what formula actually worked so people in the future searching this same question can have the answer?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.