Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2013-01-13
15
Medium Priority
?
1,552 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 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

607 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