How to Use the TIMEVALUE Function in Excel

Today, we’ll explore the TIMEVALUE function, a versatile tool in both Microsoft Excel and Google Sheets. This function is designed to convert a text string representing time into a decimal number that corresponds to that time within Excel’s date-time code system.

Basic Syntax

The TIMEVALUE function shares the same syntax across both Excel and Google Sheets:

=TIMEVALUE(time_text)

Parameters

time_text (required) – The time string you want to convert into a decimal value. It must be formatted in a way that is recognized by Excel as a valid time, for example, “12:00 PM”.

Examples

To better understand how the TIMEVALUE function operates, let’s review a few examples.

Example 1

Assume the text “10:30 AM” is in cell A1. To convert this text to a decimal value representing the time, use the following formula:

=TIMEVALUE(A1)

This will output the decimal value 0.4375, which corresponds to 10:30 AM in Excel’s date-time system.

Example 2

If cell B1 contains the time “3:45 PM”, converting it to a decimal is achieved with the formula:

=TIMEVALUE(B1)

This results in the decimal value 0.65625, which represents 3:45 PM.

Use Cases

The TIMEVALUE function is extremely useful in various scenarios, such as:

Calculating the duration between two specific times.
Conducting calculations based on time data.
Sorting or filtering datasets by time values.

Conclusion

In summary, the TIMEVALUE function in Excel and Google Sheets offers an efficient method for converting time displayed as text into numeric values usable in further calculations and data analyses. Mastery of this function can significantly enhance your proficiency in managing time-based data within your spreadsheets.

More information: https://support.microsoft.com/en-us/office/timevalue-function-0b615c12-33d8-4431-bf3d-f3eb6d186645

Other functions

DATE

Returns the serial number of a particular date

DATEDIF

Calculates the number of days, months, or years between two dates This function is useful in formulas where you need to calculate an age

DATEVALUE

Converts a date in the form of text to a serial number

DAY

Converts a serial number to a day of the month

DAYS

Returns the number of days between two dates

DAYS360

Calculates the number of days between two dates based on a 360-day year

EDATE

Returns the serial number of the date that is the indicated number of months before or after the start date

EOMONTH

Returns the serial number of the last day of the month before or after a specified number of months

HOUR

Converts a serial number to an hour

ISOWEEKNUM

Returns the number of the ISO week number of the year for a given date

MINUTE

Converts a serial number to a minute

MONTH

Converts a serial number to a month

NETWORKDAYS

Returns the number of whole workdays between two dates

NETWORKDAYS.INTL

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

NOW

Returns the serial number of the current date and time

SECOND

Converts a serial number to a second

TIME

Returns the serial number of a particular time

TODAY

Returns the serial number of today's date

WEEKDAY

Converts a serial number to a day of the week

WEEKNUM

Converts a serial number to a number representing where the week falls numerically with a year

WORKDAY

Returns the serial number of the date before or after a specified number of workdays

WORKDAY.INTL

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

YEAR

Converts a serial number to a year

YEARFRAC

Returns the year fraction representing the number of whole days between start_date and end_date