Have Questions? 
Excel Ninja Pro
All Access

February 23, 2018

6 Oclock and 12 Pm Don't Appear Correctly on Userform

[et_pb_section bb_built="1" _builder_version="3.0.105" next_background_color="#000000"][et_pb_row _builder_version="3.0.47" background_size="initial" background_position="top_left" background_repeat="repeat"][et_pb_column type="4_4"][et_pb_text _builder_version="3.0.47" background_size="initial" background_position="top_left" background_repeat="repeat"]

Sometimes using a text box on a user form or on a worksheet doesn't work exactly the way we think it should. This is especially true when were working with dates and times straight from a worksheet.

The problem we faced when helping Emil was that the format function in Excel VBA was looking at 6 AM and 12 PM as Excel thinks of these dates and times.

Excel perceives times of day as a percentage or decimal of one full day. One day equals one. 6 AM equals one fourth of one day, a.k.a. 0.25. Noon is considered halfway through the day, so Excel thinks of 12 PM as 0.5. This is where the mixup comes in.
[cc lang="vbscript" lines="-1" width="100%"]
'A1 has 06:00 AM, so excel thinks of it as 0.25
Me.Textbox1 = range("a1")

If a text boxes trying to take on the value of a cell that it perceives has the value of 0.25 (as with 6:00 AM), it's going to put something like this in the text box: "0.25".

Then, when we try to format that text box using HH: MM for hours and minutes, as in the following:
[cc lang="vbscript" lines="-1" width="100%"]
'currently Textbox1 is "0.25", so this somehow converts it to "00:25" aka 25 min after midnight!
Me.Textbox1 = Format(Me.Textbox1, "HH:MM")

So what's the solution?

I like to use the CDate() function when analyzing dates, times OR at least things that look like dates and times in order to convert them into correct date/time format. It's a handy tool.

In the aforementioned example, we just need to use CDate on the cell first to establish that it's actually 06:00 AM, not 0.25.

[cc lang="vbscript" lines="-1" width="100%"]
'this way the textbox receives the value "06:00 AM" instead of "0.25"
Me.Textbox1 = CDate( range("a1") )
'and this actually formats it correctly, as "06:00"
Me.Textbox1 = Format(Me.Textbox1, "HH:MM")

But Wait - There's more!

In an effort to be more efficient, let's try combining these two commands into one command by using the Format command on the CDate manipulated cell:

[cc lang="vbscript" lines="-1" width="100%"]
'this way the textbox receives the value "06:00 AM" instead of "0.25"
Me.Textbox1 = Format( CDate(range("a1")), "HH:MM")

Say What?!
Check out the Video here for a more Hands-on approach.

[button link="http://excelvbaisfun.com/mdocs-posts/6-oclock-and-12-pm-dont-appear-correctly-on-userform-fix/" color="default" size="" type="" shape="" target="_self" title="" gradient_colors="|" gradient_hover_colors="|" accent_color="" accent_hover_color="" bevel_color="" border_width="1px" shadow="" icon="" icon_divider="yes" icon_position="left" modal="" animation_type="0" animation_direction="down" animation_speed="0.1" alignment="left" class="" id=""]Download File[/button] [/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section][et_pb_section bb_built="1" _builder_version="3.0.105" prev_background_color="#000000" global_module="14500"][et_pb_row global_parent="14500" _builder_version="3.0.105"][et_pb_column type="4_4"][et_pb_text global_parent="14500" _builder_version="3.0.105"]

You Might Also Like

[/et_pb_text][et_pb_blog global_parent="14500" fullwidth="off" posts_number="6" include_categories="72,73,45,71,46,1,51" show_comments="on" offset_number="0" use_overlay="on" _builder_version="3.0.105" header_font="Playfair Display|700|||||||" header_font_size="20" header_line_height="1.4em" body_font="Poppins||||||||" body_font_size="16px" body_line_height="1.5em" meta_font="Poppins|600||on|||||" meta_font_size="12px" meta_text_color="#edbb5f" meta_letter_spacing="1px" meta_line_height="2em" pagination_font="Poppins|600||on|||||" pagination_font_size="16px" pagination_font_size_tablet="51" pagination_text_color="#edbb5f" pagination_letter_spacing="1px" pagination_line_height="2em" text_orientation="center" animation_style="flip" custom_css_content="display: none;"]



Excel VBA Is Fun

We believe Excel is one of the most versatile programs out there - and learning to program and automate it makes you very valuable!

You can prevent wasted time and errors, speed up tasks and become indispensable to your organization. 

See how our trainees have gotten raises, promotions and bonuses using their Excel Automation skills while building real problem-solving programs in Excel!
Copyright © 2024 ExcelVbaIsFun

Forgot Password?

Join Us

    Your Cart
    Your cart is emptyReturn to Shop
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram