Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-13
15
Medium Priority
?
1,407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 31

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 31

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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

715 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