Return date from date and time string

This tutorial shows how to extract only the date from a date and time string using an Excel formula, with the DATEVALUE and LEFT functions

Excel

Return date from date and time string using DATEVALUE and LEFT functions

EXCEL FORMULA 1. Return date from date and time string

EXCEL

Hard coded formula

Cell reference formula

=DATEVALUE(LEFT(B5,11))

=DATEVALUE(LEFT(B5,C5))

GENERIC FORMULA

=DATEVALUE(LEFT(date_time,no_char))

ARGUMENTS
date_time: The date and time string from which you want to extract only the date. This needs to be in text format.
no_char: Number of characters that make up the date in the date and time string.

GENERIC FORMULA

=DATEVALUE(LEFT(date_time,no_char))

EXPLANATION

This formula uses the DATEVALUE and LEFT functions to extract only the date from a date and time string.

The LEFT function is used to extract the relevant number of characters from the date and time string that only represent the date. Please note that for the LEFT function to work for this purpose the date and time string need to be in text format. In this example we have used the "dd mmm yyyy hh:mm:ss" text format. Given that the date is represented by the first 11 characters (dd mmm yyyy) in the date and time string this is why we have selected 11 as the number of characters to be extracted. The DATEVALUE function is then used to convert this text string into an Excel date.

Click on either the Hard Coded or Cell Reference button to view the formula that has the number of characters to be extracted from the date and time string directly entered into the formula or referenced to a specific cell.

Related Topic	Description	Related Topic and Description
Return days remaining between two dates	How to return days remaining between two dates
Return days remaining as of today	How to return days remaining as of today