automatic time and date in spreadsheet


I am using the =NOW() formula to enter current time and date, however, it enters the time in 24 hour format and I prefer 12 hour; i.e. 12:45 etc.  how to change?

2 Replies
best response confirmed by allyreckerman (Microsoft)


Select the cell with the =NOW() formula.

Press Ctrl+1 to activate the Format Cells dialog.

Activate the Number tab.

Select Date in the Category list.

Scroll through the Type list to see if there is a format you like.

If there is, select it and click OK.

If not, select the nearest one, then select Custom in the Category list.

You can edit the format code in the Type box to your liking. For example:


m/d/yyyy h:mm AM/PM


for something like 5/20/2021 9:36 PM


Then click OK.

THANKS HANS, got it and it works.