How to get minutes using this formula? The output only display 14 instead of 14:30

Alia AhmadKamal

=VALUE(MID([12 Hour Format]@row, FIND(" ", [12 Hour Format]@row) + 1, FIND(":", [12 Hour Format]@row) - (FIND(" ", [12 Hour Format]@row) + 1))) + IF(FIND("P", [12 Hour Format]@row) > 0, 12) + 6

Paul Newcome

Try this...

=VALUE(MID([12 Hour Format]@row, FIND(" ", [12 Hour Format]@row) + 1, FIND(":", [12 Hour Format]@row) - (FIND(" ", [12 Hour Format]@row) + 1))) + IF(FIND("P", [12 Hour Format]@row) > 0, 12) + 6 + "" + MID([12 Hour Format]@row, FIND(":", [12 Hour Format]@row), 3)

Alia AhmadKamal

Thanks @Paul Newcome ! do you know how to convert Pacific time(PT) to Europe time(ET) ? I try to add 9hr to that formula but some of the output is incorrect since the date will be different

Paul Newcome

Here is a thread that has numerous time based solutions. I believe one of them should be a timezone conversion. Feel free to let me know if you are unable to find or adapt something to fit your needs.

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