Excel Tip: Extract Date & DateTime from Formula

Excel Tip: Extract Date & DateTime Formula

Microsoft Excel is a powerful tool used for various data manipulation tasks. One common task is extracting dates and date-time from a given cell. In this article, we will go through the details of extracting dates and date-time using Excel formulas with a focus on the DATE function.

Introduction to Excel Date Function

The DATE function in Excel is used to return a date that is represented by a given year, month, and day. The syntax of the function is as follows:

Where:

• year - This is a number that represents the year (1900-9999)
• month - This is a number that represents the month (1-12)
• day - This is a number that represents the day of the month (1-31)

For example, the formula =DATE(2023,12,14) would return the date 14/12/2023.

Common Issue: Extracting Date from a String

A common issue when working with dates in Excel is extracting a date from a string. For example, if we have a string in a cell with the following format: "6/12/2023 21:20:34", and we only want to extract the date, we can use the following formula:

Here, the MID function is used to extract the year, month, and day from the string. However, this formula may not work in some cases and might return an incorrect result. In the following section, we will discuss an alternative solution using the DATEVALUE and TEXT functions to extract the date safely from a given string.

Alternative Solution: Extracting Date using DATEVALUE and TEXT

The DATEVALUE function in Excel is used to convert a date represented as text into a date serial number. The TEXT function in Excel formats a number as text according to a specified format. By combining these two functions, we can safely extract the date from a given string.

For example, if we have the string "6/12/2023 21:20:34" in a cell, we can use the following formula to extract the date:

Here, the LEFT function is used to extract the text from the beginning of the string up to the space character. The FIND function returns the position of the space character, and the result is passed to the DATEVALUE function to convert the text into a date serial number.

Extracting dates and date-time in Excel requires a good understanding of the DATE function, and alternative functions such as DATEVALUE and TEXT can be used to safely extract dates from strings.

References

• Excel DATE Function: https://support.microsoft.com/en-us/office/date-function-25dba1a4-2812-480b-84dd-8b32a451b3df
• Excel DATEVALUE Function: https://support.microsoft.com/en-us/office/datevalue-function-4f929606-36e5-49a4-b410-497717558f94
• Excel TEXT Function: