How to add AM/PM in googlesheet

Question

Im having trouble adding AM/PM in my formula in google sheet

This is my formula

=IF(ISDATE(H7),TEXT(NOW(),"h:mm"),"nt")

If you seend the format of NOW() is h:mm that gives you time only like 10:00

Now how can I add AM/PM after h:mm

score 2 · Answer 1 · answered Jul 27 '20 at 06:04

2

How about this modified formula?

=IF(ISDATE(H7),TEXT(NOW(),"h:mm AM/PM"),"nt")

Official document says as follows.

AM/PM for displaying hours based on a 12-hour clock and showing AM or PM depending on the time of day.
When it's 2020/07/27 15:00:00, above formula returns 3:00 PM.

answered Jul 27 '20 at 06:04

Tanaike

This one works but i have a question, I did try this, but when I add a value in a new cell the `NOW()` is updating. How can you prevent it ? – Cyrille Con Morales Jul 27 '20 at 06:48
@Cyrille Con Morales Thank you for your comment. I'm glad your issue was resolved. About your question, for example, how about converting the formula to a text using a script by executing with the OnEdit event trigger? By this, the value can be fixed. You can see the sample script at [here](https://stackoverflow.com/q/51245218). If this was not useful for your situation, I apologize. – Tanaike Jul 27 '20 at 07:13
what is mean is the `now()` function when you use it to other cell it changes the value or it is updating – Cyrille Con Morales Jul 27 '20 at 07:24
@Cyrille Con Morales Thank you for replying. I apologize my replying was not useful for your situation. When `=NOW()` is put to a cell, for example, when other cell is edited, the cell with `=NOW()` is updated. About this situation, I thought that you wanted to fix the value. So I proposed a workaround. I deeply apologize for my poor English skill. – Tanaike Jul 27 '20 at 07:31

1 Answers1