[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h3>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=TIMEVALUE(time_string)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- time_string<\/b> \u2013 is the text string that represents time in either 12-hour or 24-hour time format. Like any other formulas that take in string parameters, you should enclose the time string in double quotes. You can also use a reference to the cell that holds the time string.<\/li>\n<\/ul>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text admin_label=”Connections T – Automate beyond importrange – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”50462″ theme_builder_area=”post_content”]
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Add-on-sm_sheets-connected-new-connection.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\nAutomate data transfers beyond Importrange<\/span><\/p>\n
Find out how<\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Usage: Google Sheets TIMEVALUE<\/h3>\n
The TIMEVALUE function essentially converts a time string into a number format by expressing it as a fraction of a day. This fraction is in decimal terms, so for instance, 12:00 noon would be 0.5, since it\u2019s halfway through the 24 hour day. The output of the TIMEVALUE function is therefore a number between 0 and 1.<\/p>\n
To illustrate this further, see the example below. I have the time string of 6 pm, and I\u2019m converting it to number format.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/Google-Sheets-TIMEVALUE-1.png” alt=”Google Sheets TIMEVALUE 1″ title_text=”Google Sheets TIMEVALUE 1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In the first example here, I get the decimal value that I mentioned above. Because 6pm is \u00be of the way through a day, it is expressed in decimal form as 0.75. In the second example, I use the exact same formula but change the format of the cell using the Google Sheets toolbar. This changes the format from decimal form into a typical time form. The underlying number is still 0.75, but it\u2019s expressed as 18:00:00, which is a bit easier on the eyes!<\/p>\n
I\u2019ll show a few more examples below. The first three examples accepted direct time strings (by typing them into the formula with quotation marks) and accordingly converted them into time values. The last two examples demonstrate the TIMEVALUE formula\u2019s capability to accept cell references that have the time or date-time text strings. The time strings can be any of the following:<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- hh AM\/PM (12-hour format)<\/li>\n
- hh:mm:ss (24-hour format)<\/li>\n
- hh:mm:ss AM\/PM (12-hour format)<\/li>\n
- hh:mm (24-hour format)<\/li>\n
- hh:mm AM\/PM (12-hour format)<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/04\/2.-TIMEVALUE-example-2.png” alt=”google sheets timevalue 2″ title_text=”2. TIMEVALUE example 2″ align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
There\u2019s another interesting thing to note in the final example: the TIMEVALUE function can accept times with dates in the same cell. It simply ignores the date, and extracts the time. That can be extremely useful if you want to separate dates and times in your spreadsheet!<\/p>\n
The TIMEVALUE function is also very precise. Looking at the last example, you can see how precise the decimal number is. You can also enter times down to a fraction of a second \u2013 in the example in row 4, I specify the seconds as 46.8 seconds!<\/p>\n
You can leave the output of the TIMEVALUE function in number format, or convert it to any one of the available time formats in Google Sheets to suit your preference.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”50458″ theme_builder_area=”post_content”]
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n<\/p>\nOne tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n
Find out how<\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
When dealing with data in spreadsheets, having dates and times in string format is commonplace. While they convey the time, they may not be ideal for any kind of calculations or data processing. A way to get around that is to convert them into numerical time format. That is purpose of the TIMEVALUE formula in […]<\/p>\n","protected":false},"author":40,"featured_media":33487,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"While dealing with data in spreadsheets, getting to see the dates and times in string format is commonplace. While they convey the time, we can't use them directly for any kind of calculations or data processing. Unless we have a way to convert them into the time format. The purpose of the TIMEVALUE<\/a> formula in Google Sheets application is just that.\n
Syntax<\/h3>\nTIMEVALUE(time_string)<\/strong>\n
- \n \t
- time_string<\/strong> - is the text string that represents time in either 12-hour or 24-hour time format. Like any other formulas that take in string parameters, we should enclose the time string in double quotes. And, we can also use the reference of the cell that holds the time string.<\/li>\n<\/ul>\n
Usage: TIMEVALUE formula in Google Sheets<\/h3>\nCompared to other formulas in Google Sheets, this is certainly is one of the simplest and straightforward to use. However, it is always a good idea to approach the formula practically. Please consider the following snapshot.\n\n
![\"TIMEVALUE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/06\/TIMEVALUE-formula-Illustration-Frame-1.png\")
\n\nThe first three examples accepted direct time strings and accordingly converted them into time objects. The last two examples demonstrate the TIMEVALUE formula's capability to accept cell references that have the time or date-time text strings. The time strings can be any of the following:\n- \n \t
- hh AM\/PM (12-hour format)<\/li>\n \t
- hh:mm:ss (24-hour format)<\/li>\n \t
- hh:mm:ss AM\/PM (12-hour format)<\/li>\n \t
- hh:mm (24-hour format)<\/li>\n \t
- hh:mm AM\/PM (12-hour format)<\/li>\n<\/ul>\nPlease note that just like a date in Google Sheets is a natural number, a time is a decimal number between 0 to 1. The value 0 represents 12:00:00 AM. We illustrated this in the final example. Not only does it ignore the date component of the date-time string, it just converted the string \"13:29:32\" into a time value as shown in the cell B6.\n\nIt is interesting to note that the formula also considers milliseconds - we demonstrated this in the third example. We purposely left the final output in number format. That is to validate that the TIMEVALUE formula actually converts the time string into a numeric value.","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-8674","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/8674","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=8674"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/8674\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/33487"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=8674"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=8674"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=8674"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- time_string<\/strong> - is the text string that represents time in either 12-hour or 24-hour time format. Like any other formulas that take in string parameters, we should enclose the time string in double quotes. And, we can also use the reference of the cell that holds the time string.<\/li>\n<\/ul>\n