[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")
[/cc]
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")
[/cc]
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")
[/cc]
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")
[/cc]
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;"]
[/et_pb_blog][/et_pb_column][/et_pb_row][/et_pb_section]
