Entering Times Without a Colon

[Knarl]

I need to find a way to enter times into excel without having to use a colon.

The data I need in the cell is the hour, the minute, and either AM or PM. No seconds, which I believe is why Chip Pearson’s time VBA procedure doesn’t work for me, and I don’t know enough about VBA to change it for my needs.

So for example, I need to enter in times like 10:09 PM or 12:40 AM or 2:33 AM.

All my times will be entered into Column D.

I have done a ton of searching and tried several different solutions, but none of them allow me to put in AM or PM. If I just put 1009 into a cell with Chip Pearson’s macro, it will display as 10:09 AM, which is great if I need it to be AM. But if I need it to be PM, and I enter in “1009 p”, it will display as “1009 p”, neglecting the colon and not displaying the time correctly.

Does anyone have a solution where I can type in a 3 or 4 digit number (233 or 1240) and either “a” or “p” after it, and have it display as 2:33 PM or 12:40 AM?

P.S. I use Excel 2007, if it matters.

[khamilton]

Can you just select column D and format cells, time, then the format you wish to have??

[khamilton]

scratch the last post i dont think it is what your after sorry!!!

[Knarl]

I already have the column formatted the way I want it. But the format doesn't allow me to leave out the colon, and if I do custom formatting like 0":"00 AM/PM, it still doesn't allow me to put in AM or PM.

[DonkeyOte]

Not sure I follow...why not enter 2209 rather than 1009 P - if you did that then:

Please Login or Register  to view this content.

Right click on tab against which code is to be applied, select View Code and paste above into resulting window thereafter ensuring Macros are enabled (above should replace any existing Worksheet_Change event present)

[Knarl]

The reason why I can't enter military time is because I am entering these times off of invoices that are in standard time, and I'm entering in tens of thousands of times, and would like to enter them in the format they are in, rather than mentally convert them all into military time each time.

So an invoice I'm looking at right now has the following times:

6:16 PM
5:41 PM
2:11 PM

In excel, I want to be able to enter the following keystrokes:

616 p
541 p
211 p

And have that manifest itself in those 3 cells as:

6:16 PM
5:41 PM
2:11 PM

[DonkeyOte]

heading off and thus untested but perhaps

Please Login or Register  to view this content.

[Knarl]

That works, thanks! However, there are a couple of things that I noticed right away though.

1. Entering a midnight time (ex. 12:11 AM) results in 12:11 PM, even if I enter in "1211 A" or "1211 AM". It defaults to PM for 12 AM times.

2. I can't delete anything from the cells. If I delete the contents of the cell, it will show 12:00 AM, regardless of what time was in the cell.

Other than that, yours is the closest I've been able to get to solving my problem! I hope you or someone else can enlighten me on how to fix the 2 above issues.

[DonkeyOte]

Again I'm sure with more time it could be simplified significantly

Please Login or Register  to view this content.

[Knarl]

That's incredible. Works like a charm. Thanks so much!

Is there a way to incorporate in a similar method of entry for dates? In the same worksheet, in column C, I enter in dates with the following keystrokes:

7/21
7/1
6/29
10/11

I don't enter in the year because I just let it default to the current year, since the invoices I'm entering are in the current year. Again, because of that, Chip's date formula doesn't quite work for me.

Basically, what would be perfect is if I could enter:

721
71
629
1011

And get:

7/21
7/1
6/29
10/11

In other words, I'm trying to avoid entering in the slash. Is it possible to have something like that in the same worksheet that I have the other time colon-less formula already?

[DonkeyOte]

Just reverting to Time quickly, if you're always entering a/p then you can simplify somewhat

Please Login or Register  to view this content.

regards dates, you would need to ensure day is always two digits else you have ambiguity, eg 111 could be either 1st Nov or 11th Jan... so where day < 10 you would need to add leading zero - ie 701 such that have a consistent pattern and know categorically that right 2 digits = day.

[Knarl]

I would like to not have to enter in a/p each time. I want it to emulate excel in that all times entered default to AM, except for 12:xx times, which I want to default to PM. Basically, I want to emulate how excel works when times are entered, except I want to forgo the colon.

The problem I found with the code in the 9th post is that for 12:xx times, they default to AM now. So if I just put in "1212", instead of it becoming 12:12 PM, it becomes 12:12 AM. If I enter "1212p", it becomes 12:12 PM just fine, but I am wondering if it's possible to keep that code working exactly how it is, but change it so 12:xx times default to PM unless an "a" is entered after the time?

By the way, I love how your code works so that you don't even need to put a space in between the time and the a/p. It's really nice!

[DonkeyOte]

I'm not sure I agree with the approach given not consistent - ie for all other PM entries you need to denote p, seems odd you would not want to repeat that requirement for hour 12 entries.

Please Login or Register  to view this content.

As you can see from the various posts here there are million ways to skin this cat. I doubt any provided thus far are optimal.

[Mehul Jaganiya]

Thanks for all your solutions. They are absolutely fantastic.

According to your solution in post #9, the conversion happens only in 4th column which is column D. Is it possible to use it for multiple columns? I want to use it for column B and C.

Thanks

[HaHoBe]

Hi, Mehul Jaganiya,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Ciao,
Holger

[Sha123123]

Hi, this thread is exactly what I am trying to do but I am not sure how to apply answer #9 to my spreadsheet. Do I just copy and paste into the formula bar? Thank you.

[FDibbins]

Sha welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.