Here, we will learn how to use the Excel date and time functions: DATEVALUE and TIMEVALUE. Later, we will also learn how to preserve both date and time by combining DATEVALUE and TIMEVALUE.
Excel DATEVALUE function
The Excel DATEVALUE function converts the given date as a text string into a valid Excel date. For example, the formula =DATEVALUE("7 Jan 2025") will return 45664, which is the numeric representation of the valid date 07 January 2025. The formula in B4, copied down, is:
=DATEVALUE(A4)
Excel TIMEVALUE function
The Excel TIMEVALUE function converts the given time as a text string into a valid Excel time. For example, the formula =TIMEVALUE("9 PM") will return 0.875, which is the numeric representation of the valid time 21:00:00. The formula in E4, copied down, is:
=TIMEVALUE(D4)
Preserving both date and time by combining DATEVALUE and TIMEVALUE
To preserve both the date and time, you can combine DATEVALUE and TIMEVALUE in one formula.
The dataset (shown in the image) contains some datetimes represented as text strings in the "datetime as text" header column. The goal is to use a single formula that can preserve both the date and time and return valid datetime. The formula in B12, copied down, is:
=DATEVALUE(A12)+TIMEVALUE(A12)
How this formula works
DATEVALUE converts the given date text into a valid Excel date. TIMEVALUE converts the given time text into a valid Excel time.
You have successfully learnt how to use the Excel date and time functions: DATEVALUE and TIMEVALUE. You have also learnt how to preserve both date and time by combining DATEVALUE and TIMEVALUE! I hope this post helped you.
