Solved

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

Posted on 2013-01-13
15
1,204 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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
 

Author Comment

by:"Abys" Wallace
Comment Utility
@ 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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:"Abys" Wallace
Comment Utility
@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
Comment Utility
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
Comment Utility
Brain,

ok, thanks.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
tonya,

OK, this now edits times.

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

Author Comment

by:"Abys" Wallace
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Glad to help, Tonya!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now