In Excel, the DATEVALUE and TIMEVALUE functions are important tools for working with date and time data, as they are able to convert textual representations of date and time into numeric values for various calculations and analyses.
Today, let's take a closer look at the principles and applications of these two functions.
DATEVALUE 函数原理
THE DATEVALUE FUNCTION CONVERTS THE LITERAL REPRESENTATION OF THE DATE INTO A SERIAL NUMBER THAT REPRESENTS THE NUMBER OF DAYS STARTING JANUARY 1, 1900.
FOR EXAMPLE, ENTER "7/22/2024" AND THE DATEVALUE FUNCTION WILL CONVERT IT TO 45098 BECAUSE JULY 22, 2024 IS 45098 DAYS AWAY FROM JANUARY 1, 1900.
其语法结构为：=DATEVALUE(date_text)。
Parameter date_text is required, the text that represents the date in Excel date format, or the cell reference to the cell in which the text represents the date in Excel date format. The following points need to be noted:
date_value The parameters of the function must be entered in text format, and the date must be in double quotation marks, otherwise the error value "#VALUE!" will be returned.
If you omit the year in the parameter, it defaults to the current year.
If the date parameter contains time information, the DATEVALUE function will not return it. However, if the date contains invalid time information, such as "89:98", an error will occur.
TIMEVALUE 函数原理
THE TIMEVALUE FUNCTION CONVERTS A LITERAL REPRESENTATION OF TIME INTO A DECIMAL VALUE THAT REPRESENTS THE PROPORTION OF TIME IN A DAY.
FOR EXAMPLE, ENTER "13:30:00" AND THE TIMEVALUE FUNCTION WILL CONVERT IT TO 0.5625 BECAUSE 13:30 IS 56.25% OF THE DAY (13.5 / 24 = 0.5625).
其语法为：=TIMEVALUE(time_text) 。
Parameter time_text is a string of text that represents time in Excel time format, such as "2:15 PM", "14:15", and so on. When using this function, you need to pay attention to the following:
Parameters must be entered in text format and the time must be in double quotation marks.
Parameter time_text can use either a 12-hour or 24-hour time format, such as "2:15 PM" and "14:15" are valid time expressions.
If the time parameter contains date information, the TIMEVALUE function does not return date information. However, if the time parameter contains invalid date information, an error is returned.
Application in datetime calculations
Application 1: Calculate the date interval
LET'S SAY WE HAVE TWO DATES, "2024/7/1" AND "2024/7/22", USE THE DATEVALUE FUNCTION TO CONVERT THEM TO NUMERIC VALUES, AND THEN SUBTRACT THEM TO GET THE NUMBER OF DAYS BETWEEN THESE TWO DATES.
=DATEVALUE("2024/7/22") - DATEVALUE("2024/7/1")
Such as calculating the duration of the project. Let's assume that a project starts on "2024/7/15" and ends on "2024/8/5".
START DATE VALUE = DATEVALUE("2024/7/15")
结束日期数值 = DATEVALUE("2024/8/5")
持续天数 = DATEVALUE("2024/8/5") - DATEVALUE("2024/7/15")
Application 2: Addition and subtraction of time
AFTER USING THE TIMEVALUE FUNCTION TO CONVERT TIME TO A NUMERIC VALUE, YOU CAN ADD OR SUBTRACT IT.
For example, add 2 hours to "13:30:00".
=TIMEVALUE("13:30:00") + TIMEVALUE("2:00:00")
For example, calculate the working hours of employees. The employee's clock-in time is "2024/7/22 8:30:00" and the clock-out time is "2024/7/22 17:00:00".
上班时间数值 =TIMEVALUE("8:30:00")
下班时间数值 =TIMEVALUE("17:00:00")
工作时长 =TIMEVALUE("17:00:00") - TIMEVALUE("8:30:00")
DATEVALUE、TIMEVALUE函数使用技巧
Date format consistency. Make sure that the date text format you enter into the DATEVALUE function is a common format that Excel can recognize, such as "YYYY/MM/DD", "YYYY-MM-DD", and so on, to avoid conversion errors.
Handle date formats for different regions. Excel may have a different default understanding of date formatting depending on your system locale. If you encounter conversion issues, you can fix them by customizing the format or explicitly specifying a date separator.
Combine with other date functions. Use the DATEVALUE function in conjunction with functions such as YEAR, MONTH, DAY, etc., to extract information such as year, month, day, etc. in the date.
Time accuracy control. WHEN USING THE TIMEVALUE FUNCTION, IF YOU ONLY NEED TO BE ACCURATE TO THE HOUR OR MINUTE, YOU CAN SIMPLIFY THE TIME TEXT YOU ENTER, FOR EXAMPLE, JUST ENTER "15:30" INSTEAD OF "15:30:00".
Batch processing. If you need to convert a column or row of date or time text, you can quickly batch process by dragging the fill handle or using an array formula.
Check for errors. After using these two functions, check that the results are as expected. If you get an incorrect value, such as #VALUE!, check that the text you entered is formatted correctly.
Data validation. When entering date and time text data, use the data validation function to limit the format of user input to reduce errors.
Works with conditional formatting. Combine the converted date or time values with conditional formatting to highlight data for a specific date range or time range.
Dynamic referencing. to put the text input of the date or time in a separate cell, and then reference the cell in the function for easy change and maintenance.
Cross-table references. WHEN USING THE DATEVALUE AND TIMEVALUE FUNCTIONS IN MULTIPLE WORKSHEETS, PAY ATTENTION TO REFERENCING THE WORKSHEETS AND CELLS CORRECTLY.
The DATEVALUE and TIMEVALUE functions provide great convenience for us to work with date and time data in Excel, and by using these two functions flexibly, we can easily complete various complex datetime calculations.