Excel VBA- Convert Time Entries into "##:## AM/ PM" format

Hi Experts,

I was looking for a way to convert user entries into a 12 hour AM/ PM time format.  Was hoping after the end user enters the time in the text field as follows:

"1234" ... It'll automatically convert it to "12:34" and then give the option to select "AM/PM" after the 4th digit is entered.

Example:
If the end user enters "0123" it'll convert to "01:23" then auto prompt "AM/PM" in a mini drop down window.   Something similar to a calendar pop.  If not just converting it and allowing room for the end user to enter AM or PM will suffice.

The user form can be called by clicking on the "black" RPV title above the data entry table.

Thank you for your help!
Sample-Workbook.xlsm
"Abys" Wallaceself employedAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
redmondbConnect With a Mentor Commented:
Tonya,

Sorry, I thought that was what you wanted - to be the same as Excel?!

Please see attached. (Note: if the time is 12:00 to 12:59 then either "A" or "P" is accepted.)

Regards,
Brian.Sample-Workbook-V5.xlsm
0
 
gowflowCommented:
Is it an option to change the textbox of the time to a datepicker control formated as time.
gowflow
0
 
redmondbCommented:
Hi, abys757.

Alternatively, would you accept the user entering something in the format...
####x
...where "x" is "A" or "P"? So a message would be displayed if they omitted a valid letter.

Regards,
Brian.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
"Abys" Wallaceself employedAuthor Commented:
@gowflow not sure if there is an option to change the datepicker control formatted as time for the text box.  I would rather they manually enter the numbers in a 4 digit format  #### and have it auto-add the colon ##:## ... and for the AM / PM portion I guess I would rather have a drop down appear and have the end user pick from the 2 options rather than a pop...
0
 
"Abys" Wallaceself employedAuthor Commented:
@ redmondb  Hi Brian... yes I would accept the user entering something in the ####x format...  That would most likely work better...  Thanks~

Tonya
0
 
gowflowCommented:
Well I tried to make it simple for you and avoid the user having to enter A or P and here is how it works:
Enter any value in the time textbox (without the :) example
234 for 2:34 AM
432 for 4:32 AM
1654 for 4:54 PM
and any other value that could not be a time like 678 or 999 etc ... and see the outcoume.

Let me know if this is what you want. We can cater it depending on ur reply.

NOTE they even don't need to enter it in a 4 digit format 3 would be enough obviously for AM and 4 for PM
gowflow
Copy-of-Sample-Workbook.xlsm
0
 
"Abys" Wallaceself employedAuthor Commented:
@gowflow  Thank you ... it works great but my only dilemma is majority of the end users aren't that good with military time so they enter their data 0430 PM for 4:30 PM instead of 1630 ..  

Is there a way they can enter 0430 and auto pop a drop list for the AM / PM with your code?

Thank you..
0
 
redmondbCommented:
Tonya,

My current version. I'm changing it to check that minutes are <- 59, but let me know if it's otherwise OK.

Thanks,
Brian.
Sample-Workbook-V2.xlsm
0
 
"Abys" Wallaceself employedAuthor Commented:
@redmondb  Brian, your code works great but when I enter something like "1159a" it generates "11:59PM"  same thing happens when I enter "1200A" it'll produce "12:00PM" ...  Other morning timeframes work.  Is it something I'm doing wrong?

Thanks tonya
0
 
redmondbCommented:
tonya,

when I enter something like "1159a" it generates "11:59PM"
Apologies, I inadvertently used cInt instead of Int and the value was rounding. Fixed now.

when I enter "1200A" it'll produce "12:00PM"
Cold-bloodedly deliberate - that's what Excel does! (Try entering 12 into B3.)

Back with the time edit shortly.

Regards,
Brian.
Sample-Workbook-V3.xlsm
0
 
"Abys" Wallaceself employedAuthor Commented:
Brain,

ok, thanks.
0
 
redmondbCommented:
tonya,

OK, this now edits times.

Regards,
Brian.
Sample-Workbook-V4.xlsm
0
 
"Abys" Wallaceself employedAuthor Commented:
Brian,

the same issue is still occurring .. I'll type in "1200A" and it'll generate "12:00PM" anything with the "12" in the hours section.  Tried putting "1200AM" to see if it'll take but it wasn't the correct format...

Thanks for your time,

Tonya
0
 
"Abys" Wallaceself employedAuthor Commented:
No worries...  Apologies if I wasn't clear... Works like a charm... Definitely appreciate your time working through it!  :)

Kindest Regards,

Tonya
0
 
redmondbCommented:
Glad to help, Tonya!
0
All Courses

From novice to tech pro — start learning today.