Solved how do I drop the :00 in a time cell?

October 30, 2014 at 22:11:51
Specs: Windows 7
I am calling time data that appears as 2:00 AM. How can I change it to appear as 2am in the cell? Ideally, I would like to add something that would insert the time zone this way:

2am ET

This is info pulled from an API into Google Sheets.

Also, I may be in Win XP, not Win 7.


message edited by kc64

See More: how do I drop the :00 in a time cell?

Report •

October 31, 2014 at 02:27:09
Use a custom date format:

Report •

October 31, 2014 at 08:37:33
✔ Best Answer
I don't use Google sheets, so the best I can offer is a Custom Cell Format for Excel, which gets you close, but not exact:

This Custom Cell Format...

h AM/PM "ET"

...will return...


I also want to point out one possible issue:

Very often, Time and Date data that is downloaded into Excel from other applications or perhaps a website look like Times and Dates, but are often simply text. Regardless of how you try to format the cell, there are times when you cannot change the format and turn the text into a Time/Date. You can go through the normal steps to change the format, but it won't change the way the contents of the cell are displayed.

I don't know if Google Sheets will exhibit this issue, but I just want you to be aware of it so that you don't pull your hair out trying to figure out why your formatting attempts aren't working.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question