Lesson#252: DATEVALUE and TIMEVALUE Functions: Unleashing the Power of Date and Time Conversion

In the realm of Excel functions, there are often scenarios where you need to work with date and time data. This is where the DATEVALUE and TIMEVALUE functions come into play as your trusty allies. These functions enable you to convert date and time text into usable values, making data manipulation a breeze. In this expert-level guide, we’ll delve into the intricacies of DATEVALUE and TIMEVALUE, showcasing their significance and versatility. With more than 15 practical examples, you’ll be well-equipped to master the art of date and time conversion.

Understanding DATEVALUE and TIMEVALUE Functions

Let’s begin by demystifying the core concepts of DATEVALUE and TIMEVALUE:

• DATEVALUE Function: This function converts a date in text format into a serial number representing the date in Excel. The text must be in a recognizable date format.
• TIME VALUE Function: Similar to DATEVALUE, this function transforms a time in text format into a serial number representing the time in Excel. The text must be in a recognizable time format.

These functions are particularly useful when dealing with data imported from external sources or when you need to perform calculations based on date and time. Now, let’s dive into practical examples to see how these functions work.

Step-by-Step Tutorial

Example 1: Converting Text Date to Date Value

Imagine you have a column of text-based dates, and you want to convert them into Excel date values.

Table: Text-Based Dates

Text Date
January 1, 2023
12/15/2023
05-Mar-22

Using DATEVALUE Function:

=DATEVALUE(A2)

In this formula:

• A2 is the cell containing the text date.
• The DATEVALUE function converts the text into a serial date value.

Example 2: Converting Text Time to Time Value

Suppose you have a column of text-based times and you want to convert them into Excel time values.

Table: Text-Based Times

Text Time
8:30 AM
16:45
2:15 PM

Using TIMEVALUE Function:

=TIMEVALUE(A2)

In this formula:

• A2 is the cell containing the text time.
• The TIMEVALUE function converts the text into a serial time value.

Example 3: Handling Date and Time Together

Let’s say you have text data with both date and time information, and you want to split them into separate date and time values.

Table: Text-Based Date and Time

Text Date and Time
2023-06-15 10:30 AM
05/20/22 13:45
Mar 8, 2024 17:00

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(LEFT(A2, 10))

For Time:

=TIMEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

In these formulas:

• We use the LEFT function to extract the date portion (first 10 characters) and the RIGHT function combined with FIND to extract the time portion.

Example 4: Converting Date and Time in Different Formats

Suppose you have text dates and times in various formats and you want to standardize them into Excel date and time values.

Table: Text Dates and Times in Different Formats

Text Date and Time
01-15-2023 10:30 AM
05/20/22 01:45 PM
March 8, 2024 5:00 PM

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"-","/"),",",""))

For Time:

=TIMEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

In these formulas:

• For the date, we use SUBSTITUTE to replace hyphens and commas with slashes (a format recognized by DATEVALUE).
• For time, we follow the same approach as in Example 3 to extract the time portion.

Example 5: Converting Dates with Months in Text Format

Imagine you have dates where months are written in text format, and you want to convert them to Excel date values.

Table: Text Dates with Months in Text Format

Text Date
June 15, 2023
20-May-22
Mar 8, 2024

Using DATEVALUE Function:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2, ",", ""), "-", " "))

In this formula:

• We use SUBSTITUTE to remove commas and hyphens, making the date text recognizable by DATEVALUE.

Example 6: Converting Time with AM/PM

Suppose you have text times in a 12-hour format (AM/PM) and you want to convert them into Excel time values.

Table: Text Times in 12-Hour Format

Text Time
10:30 AM
03:45 PM
08:15 AM

Using TIMEVALUE Function:

=TIMEVALUE(SUBSTITUTE(A2, " AM", "AM"))

In this formula:

• We use SUBSTITUTE to remove the space before “AM,” making the time text recognizable by TIMEVALUE.

Example 7: Converting Time with Different Formats

Let’s say you have text times in various formats, and you want to standardize them into Excel time values.

Table: Text Times in Different Formats

Text Time
10:30 AM
16:45
2:15 PM

Using TIMEVALUE Function:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2, " AM", "AM"), " PM", "PM"))

In this formula:

• We use SUBSTITUTE to remove the space before “AM” and “PM,” making the time text recognizable by TIMEVALUE.

Example 8: Handling Date and Time with Different Separators

Suppose you have text dates and times with different separators (e.g., “/”, “-“, or “.”), and you want to convert them into Excel date and time values.

Table: Text Dates and Times with Different Separators

Text Date and Time
06/15/2023 10.30 AM
05-20-22 01-45 PM
03.08.2024 05.00 PM

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ".", "/"), "-", "/"), " ", "/"))

For Time:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(A2, LEN(A2) - FIND(" ", A2

)), ".", ""), "-", ""))

In these formulas:

• For the date, we use SUBSTITUTE to replace all separators with slashes (a format recognized by DATEVALUE).
• For time, we follow the same approach as in previous examples, extracting the time portion and removing separators.

Example 9: Handling Date and Time with Text Months

Imagine you have text dates with months in text format, and you want to convert them into Excel date and time values.

Table: Text Dates with Text Months

Text Date and Time
June 15, 2023 10:30 AM
May 20, 2022 01:45 PM
March 8, 2024 05:00 PM

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2, ",", ""), " ", "-"))

For Time:

=TIMEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

In these formulas:

• For the date, we use SUBSTITUTE to remove commas and replace spaces with hyphens (a format recognized by DATEVALUE).
• For time, we extract the time portion as in previous examples.

Example 10: Handling Date and Time with Different Formats

Suppose you have text dates and times in various formats and you want to standardize them into Excel date and time values.

Table: Text Dates and Times in Different Formats

Text Date and Time
2023-06-15 10:30 AM
05/20/22 01:45 PM
Mar 8, 2024 5:00 PM

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","/"),",","")," ","-"))

For Time:

=TIMEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

In these formulas:

• For the date, we use SUBSTITUTE to replace hyphens, commas, and spaces with slashes and hyphens (a format recognized by DATEVALUE).
• For time, we extract the time portion as in previous examples.

Example 11: Handling Date and Time with Non-Standard Separators

Let’s say you have text dates and times with non-standard separators and you want to convert them into Excel date and time values.

Table: Text Dates and Times with Non-Standard Separators

Text Date and Time
06
05^20^22 01
03-08-2024 05^00 PM

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","/"),",",""),"^","/")," ","-"))

For Time:

=TIMEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

In these formulas:

• For the date, we use SUBSTITUTE to replace hyphens, commas, carets, and spaces with slashes and hyphens (a format recognized by DATEVALUE).
• For time, we extract the time portion as in previous examples.

Example 12: Handling Date and Time in Different Languages

Suppose you have text dates and times in different languages, and you want to convert them into Excel date and time values.

Table: Text Dates and Times in Different Languages

Text Date and Time
15 juin 2023 10:30 AM
20 mai 2022 13:45
8 März 2024 17:00

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"janvier","January"),"février","February"),"März","March"),"mai","May"),"juin","June")," ","-"))

For Time:

=TIMEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

In these formulas:

• For the date, we use SUBSTITUTE to replace months in different languages with their English counterparts, and we replace spaces with hyphens (a format recognized by DATEVALUE).
• For time, we extract the time portion as in previous examples.

Example 13: Handling Date and Time with Custom Formats

Suppose you have text dates and times in custom formats, and you want to convert them into Excel date and time values.

Table: Text Dates and Times in Custom Formats

Text Date and Time
2023$06$15 10@30 AM
05#20#22 01*45 PM
Mar82024 5#00 PM

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$","/"),"#","/"),"@",":"),"*","/")," ","-"))

For Time:

=TIMEVALUE(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

In these formulas:

• For the date, we use SUBSTITUTE to replace custom separators with slashes and colons, and we replace spaces with hyphens (a format recognized by DATEVALUE).
• For time, we extract the time portion as in previous examples.

Example 14: Handling Dates and Times with Additional Text

Let’s say you have text data with additional text surrounding the date and time, and you want to extract the date and time values.

Table: Text Data with Additional Text

Text Data
Date: June 15, 2023 10:30 AM
Event Date (05/20/22) Time: 01:45 PM
Meeting on Mar 8, 2024 at 5:00 PM

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(MID(A2, FIND(":", A2) + 2, LEN(A2) - FIND(":", A2) - 2))

For Time:

=TIMEVALUE(MID(A2, FIND("Time:", A2) + 6, LEN(A2) - FIND("Time:", A2) - 6))

In these formulas:

• For the date, we use the MID function combined with FIND to extract the date portion.
• For time, we do the same to extract the time portion.

Example 15: Handling Dates and Times in Different Orders

Imagine you have text data where dates and times appear in different orders, and you want to extract both values.

Table: Text Data with Different Orders

| Text Data |
|—————-

——-|
| 10:30 AM on June 15, 2023 |
| 01:45 PM (05/20/22) Event Time |
| 5:00 PM Meeting (Mar 8, 2024) |

Using DATEVALUE and TIMEVALUE Functions:

For Date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(MID(A2, FIND("on", A2) + 3, LEN(A2) - FIND("on", A2) - 2), "(", ""), ")", ""))

For Time:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(A2, FIND("on", A2) + 3, LEN(A2) - FIND("on", A2) - 2), "(", ""), ")", ""))

In these formulas:

• For the date, we use the MID function combined with FIND to extract the date portion. We also use SUBSTITUTE to remove parentheses.
• For time, we do the same to extract the time portion and remove parentheses.

Conclusion

The DATEVALUE and TIMEVALUE functions are powerful tools that can help you tackle various scenarios when working with date and time data in Excel. Whether you’re converting text-based dates and times from different formats, handling text months, or dealing with custom separators, these functions are your key to converting and manipulating data effectively. By mastering DATEVALUE and TIMEVALUE, you’re well on your way to becoming an Excel data manipulation expert.

Stay updated with Excelabcd. Join our WhatsApp channel.

https://whatsapp.com/channel/0029Va5mZ9V1nozBRRpKbj38