I recently was challenged to display the time in an Excel spreadsheet, using a 12-hour format, but without showing the AM/PM portion of the time. Instead, just showing 3:30 instead of 3:30 PM.
If you've ever formatted a cell with time, you know that adding "AM/PM" to the end of the format will result in changing from a 24-hour clock (15:30) to a 12-hour clock (3:30 PM). However, we don't want to see the PM in our solution. But if you don't include "AM/PM" in the formula, it will return the date as 15:30 instead of 3:30. So, how do we get around this?
In my opinion, the easiest way to achieve this is to include the AM or PM, and remove it using the SUBSTITUTE function. Assuming the current time is 15:30, If you were to use the following formula,
=TEXT(NOW(),"h:mm am/pm") the result would be 3:30 PM.
However, by adding the SUBSTITUTE function to our formula, we can remove the AM or PM...
=SUBSTITUTE(TEXT(SUBSTITUTE(TEXT(NOW(),"h:mm am/pm")," AM",""),"h:mm am/pm"), "PM","")
This will replace all instances of AM or PM with empty text.
SUBSTITUTE is used twice, once to remove of AM, and the other to remove PM
Keep in mind that the default Excel formula uses uppercase letters for
AM and PM, so your substitution code must also use uppercase if you want
to see them replaced. The code will find AM, but not am.
Also, the formatting has a space between the time and AM (or PM), so if you want to see that space removed as well, you need to add it to your substitution formula. One more thing to note, if you use one h in the formula, it will return 3:30. However, two h's in the formula would return 03:30. It's just a matter of preference, one or two number spaces for the hour.
If you have a better solution or have an Excel problem you'd like
to see solved, feel free to contact me, and I will add it to this page.
