Cell Formatting/Input Mask date as text with AM/PM

[jpsanicky]

Greetings,
I need to format cells to input a time and am or pm using numbers and letters.
1030a to return 10:30AM
530p to return 5:30PM
Uppercase if possible.
I am using #":"## to return a military format. 1825 returns 18:25.
I can use this format with this formula to aid in making calculations between start and stop times. =--TEXT(C10,"00\:00")
When I try to add other items to the mask, I get errors, or it doesn't work.
#":"##_@"m" This yields 10:30 m I need to get the a in there, too.
Thanks,
Jerry

 

[BradleyS]

If you format the cells as hh:mm AM/PM
When you enter the time using a colon as the divider i..e 10:30 it will show as 10:30 AM. and 15:30 as 15:30PM

 

[BradleyS]

Greetings,
I need to format cells to input a time and am or pm using numbers and letters.
1030a to return 10:30AM
530p to return 5:30PM
Uppercase if possible.
I am using #":"## to return a military format. 1825 returns 18:25.
I can use this format with this formula to aid in making calculations between start and stop times. =--TEXT(C10,"00\:00")
When I try to add other items to the mask, I get errors, or it doesn't work.
#":"##_@"m" This yields 10:30 m I need to get the a in there, too.
Thanks,
Jerry

Or do you want something like this:

autofilterTest.xlsm
	F	G
1	Enter Time	Output
2	1030a	10:30 AM
Sheet1

Cell Formulas
Range	Formula
G2	G2	=IF(LEN(F2)=5,LEFT(F2,2)&":"&MID(F2,3,2)&" "&IF(RIGHT(F2,1)="a","AM","PM"),LEFT(F2,1)&":"&MID(F2,2,2)&" "&IF(RIGHT(F2,1)="a","AM","PM"))

 

[jpsanicky]

Bradley S
Thanks for the response.
Ideally, I want all the input masks to be in the same cell. This way, the user doesn't have to worry about typing a ":".
Enter a time as 1030a and get 10:30 AM in the same cell.

If this is not possible, then that's what it will be.

Using the format of #":"## when entering 24-hour time, I get the correct format 1030 = 10:30 and 2210 - 22:10, and I can run calculations against the data.
Maybe I can use your example in an input cell and mask the input values when entered with conditional formatting.
I want to avoid using too much code in this project.
Jerry

 

[BradleyS]

Bradley S
Thanks for the response.
Ideally, I want all the input masks to be in the same cell. This way, the user doesn't have to worry about typing a ":".
Enter a time as 1030a and get 10:30 AM in the same cell.

If this is not possible, then that's what it will be.

Using the format of #":"## when entering 24-hour time, I get the correct format 1030 = 10:30 and 2210 - 22:10, and I can run calculations against the data.
Maybe I can use your example in an input cell and mask the input values when entered with conditional formatting.
I want to avoid using too much code in this project.
Jerry

That will require VBA code, if you want it to function within the same input cell.