Display times in a 12-hour format without the AM or PM displayed in the cell

I've tried many different custom formats, but can't get 12 hour time to display without the AM or PM displayed in the cell. Perhaps if there isn't a custom time format to accomplish this, an IF formula could be used. I've tried to figure out how that could be accomplished but don't understand enough about IF functions yet, but here's my attempt at an explanation of what I imagine an IF formula could do.

IF C4 =13:00 then -12:00 (this would bring the time display to 1:00) I would then copy down that formula.

Now, if the IF formula is the best solution, could the cells in the time display column contain the time plus the IF formula?, or would I need to create an additional time column that I would feed off but hide when I have the page developed?

My goal here is to keep the time column as narrow as possible to leave more room to write in the adjacent cell, while still seeing all days on the calendar page.

Also, for the "Start" time to be easily customizable, all a person needs to do is change the first time on Monday (let's say from 5am to 6am), and all times automatically update.

The file I've included does this now, but as mentioned, all time are 24 hours, and I want them in 12 hour format.

I've also included an extra column on the left if the IF formula is the way to go here.

Any help would be greatly appreciated.

Thanks,
Bryan

I would probably go with Aardigspook's formula, but I just wanted to show you there is almost always more than one way to do anything in Excel...

=MOD(A1-0.01,0.5)+0.01

Originally Posted by Aardigspook

@Rick: You're quite right that there are often many ways of doing something in Excel, but note that using MOD will make 12:30 pm display as 0:30.

Bad testing on my part. This should work correctly though...

=MOD(A1-1/24,0.5)+1/24

Hi Rick,
Thanks for taking the time to provide another solution. I appreciated that. I didn't know about the MOD Function, so I'll look into it further. Always good to learn!
As well, for following up on Aardigspook's 0:30 comment. : )

Thanks again Aardigspook, I have an inquiry for you if you are in the mood.

I've attached another spreadsheet to the workbook that has the calendar file you modified for me. The included spreadsheet is part of a completed finance workbook that I received a lot of help from forum members to create, so I don't understand how everything is working, but at this point, it's just important that it does work!

I referrered to the spreadsheet I'm including when trying to figure out how an IF formula might work for the time format problem that you have now solved. What I'm wondering about is this: You will see an IF formula in the months columns of the included spreadsheet. So, I'm wondering how it is that the column is displaying the actual number of months, even though it has the IF formula in it. I can't see/remember how this was accomplished. I'm wondering (since this is possible) if it is also possible to display the times in the calendar spreadsheet as well as the IF function in just one column.

There would be some advantages to this, for example, it would make it a little "tidier" to allow each day of the week to have a different start time without each day having a hidden column.

The spreadsheet I gleaned this from has 15 creditor columns, but I just included Creditor 1 since it's all that's needed for my inquiry.

Hi Rick,
I wanted to let you know that your 2nd MOD formula solution is actually the one I'm using. The reason is this: When I changed the start time on the calender to 7:00 (for example), the 2nd time the 13:00 appeared, it did not convert to 12:00 using Aardigspook formula for some reason, which is the reason I tried yours. So, thanks, your extra effort is much appreciated!

Perhaps you wouldn't mind taking a look at a reply I sent to Aardigspook re an (additional) inquiry about the possibility of having the time and formula in the same cell. I included a file that shows where I show the days of the month, that also includes an IF formula, but, as explained, I had help from forum members to do this and I can't remember or figure out how it works.

Wow, Aardigspook, thank you so much for the thorough explanation re my inquiry, and for the time you put into this. So appreciated! Also, for providing me with a working file. Just great. It's so interesting to me how the open and collaborative efforts are usually the ones that bring the best results.

The singular columns are so helpful and allow for more adaptability.

I was also wondering how you inserted those little arrows in the Start time cell, and was about to ask you and then decided to do a google search on how to do it. Found the answer so I now understand how to add the arrows with symbols. It's a nice touch and I appreciate learning that because of you.

Hope you have a great day, and thank again!

Thanks for letting me know about Alt-codes and the Windows Character Map. That will come in handy. And memorizing such things is a good thing, faster and good brain exercise as well, so nothing "sad' about that .

I noticed you are Forum Expert with just 2,184 posts, so I'm assuming you achieve such status through members adding 'reputation' is that correct? This must mean you've been very helpful to others in your responses. I'm mentioning this because I've noticed other forum Experts and Gurus have very high post numbers.

Since you really seem to know your stuff, I was wondering if you know if it is possible to embed a cell style so that when you move that cell, the original cell maintains the same format style. I have been able to achieve this (in part) by highlighting an entire row of cells, then setting the style. For example the Routine Calendar I posted is like this, but it only works on one cell style, so adjacent columns or cells (for example) cannot be customized through (for example) highlighting an entire column and setting a different style. Of course it will set the style, but if the cells are moved, the style "goes with it". You will notice you can write and then move the entry cells on the Routine Calendar, and when you write again in the original cell, the original format has been maintained. But if you move a cell in a time column, the original style is "lost".

Well, I stand corrected. I just test this again, and Excel does allow column custom cell styling as well as the row method I mentioned in my last post. This did not work on the Mac 2011 version, but it works on both the 2016 Windows and Mac versions. So there you go... Should have checked this more thoroughly before my last post...

Closing Thought: You helped because I thought you could, and in my query to you ended up figuring it out to make sure my query was accurate, which I wouldn't of even tried if I didn't think you might have the answer...

Hi Aardigspook,
While working on the calendar form you helped me with I noticed something odd that I thought you might be interested in seeing. I'm sending back the file you sent me, wherein I thought the 1:00/13:00 display had been solved, and for the most part it has. But...when copying the formula over to a new spreadsheet calendar format, I noticed the 13:00 popping up. I thought I was doing something wrong, until I noticed that all start times didn't respond the same re the 1:00/13:00. So I looked more closely at the file you sent and I noticed the same thing, which you will see as well. I've highlighted where the 13:00 is popping up in some of the columns, and the strange thing is, even in the same column, 1:00 appears in the next 12 hour cycle after a 13:00. You will also notice I highlighted the 1:00 times on the 3rd day, where there is no problem, 1:00 appears in both 12 hour cycles. Basically, you will see that the formula isn't consistent, sometimes it display 1:00 sometimes 13:00, and as mentioned, this can happen in the same column. Any ideas on a fix?

Good point. Thanks. The SOLVED tag has been removed.

Thanks Aardigspook for providing the remedy so promptly! It was also thoughtful of you to highlight all the 1:00s.
I hope you have good day.
Bryan