Solved

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

Posted on 2013-01-13
15
1,245 Views
Last Modified: 2013-01-13
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
0
Comment
Question by:"Abys" Wallace
  • 7
  • 6
  • 2
15 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38772386
Is it an option to change the textbox of the time to a datepicker control formated as time.
gowflow
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772458
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
 

Author Comment

by:"Abys" Wallace
ID: 38772705
@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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:"Abys" Wallace
ID: 38772708
@ redmondb  Hi Brian... yes I would accept the user entering something in the ####x format...  That would most likely work better...  Thanks~

Tonya
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38772755
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
 

Author Comment

by:"Abys" Wallace
ID: 38772768
@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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772782
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
 

Author Comment

by:"Abys" Wallace
ID: 38772792
@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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772807
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
 

Author Comment

by:"Abys" Wallace
ID: 38772811
Brain,

ok, thanks.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772816
tonya,

OK, this now edits times.

Regards,
Brian.
Sample-Workbook-V4.xlsm
0
 

Author Comment

by:"Abys" Wallace
ID: 38772820
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
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38772863
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
 

Author Closing Comment

by:"Abys" Wallace
ID: 38772868
No worries...  Apologies if I wasn't clear... Works like a charm... Definitely appreciate your time working through it!  :)

Kindest Regards,

Tonya
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772883
Glad to help, Tonya!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question