Change Time into Military

Adrian Garcia

https://community.smartsheet.com/discussion/comment/284742#Comment_284742

It worked but now I'm getting an extra number on every row, even the ones that are not three digits.

So instead of 5 hours is giving me 50 and for 5.5 is giving me 59.5.

=(VALUE(LEFT([End Military]@row, IF(LEN([End Military]@row) = 3, 1, 2)) + VALUE(RIGHT([End Military]@row, 2)) / 60) - (VALUE(LEFT([Start Military]@row, IF(LEN([Start Military]@row) = 3, 1, 2)) + VALUE(RIGHT([Start Military]@row, 2)) / 60)))

Paul Newcome

@Adrian Garcia Are you able to provide a screenshot of the time columns?

Adrian Garcia

https://community.smartsheet.com/discussion/comment/284782#Comment_284782

Here is the formula I'm using for military time:

=VALUE(IF(VALUE(LEFT([Demo Start Time]@row, FIND(":", [Demo Start Time]@row) - 1)) <> 12, VALUE(LEFT([Demo Start Time]@row, FIND(":", [Demo Start Time]@row) - 1)) + IF(CONTAINS("p", [Demo Start Time]@row), 12), IF(CONTAINS("p", [Demo Start Time]@row), 12, 0)) + MID([Demo Start Time]@row, FIND(":", [Demo Start Time]@row) + 1, 2))

Paul Newcome

I'm not sure why it is doing that. Try this one with a couple of extra parenthesis tucked in to help isolate certain pieces.

=(VALUE(LEFT([End Military]@row, IF(LEN([End Military]@row) = 3, 1, 2)) + (VALUE(RIGHT([End Military]@row, 2)) / 60)) - (VALUE(LEFT([Start Military]@row, IF(LEN([Start Military]@row) = 3, 1, 2)) + (VALUE(RIGHT([Start Military]@row, 2)) / 60))))

Adrian Garcia

https://community.smartsheet.com/discussion/comment/284793#Comment_284793

Same result but thanks for your help, atleast the negative values are gone. I'll keep working on it to see if I can find a solution that would get rid of that problem.