Timestamps in Excel represent DateTime values that include both date and time in a single cell. If you want to separate timestamps into time and date, Excel provides many functions like DATE, TEXT, etc.
In this article, we will learn how to convert timestamps to date in Excel. Read the article until the end because we will discuss various methods and functions that will enhance your skills in handling time and dates in Excel.
You can click on the following links to jump to a specific section of the article:
- Using the DATE function
- Using the TEXT function
- Using Cell Formatting
- Using the Text to Columns tool
Download the Example Excel Sheet
Consider downloading the following Excel spreadsheet containing the dataset we used to demonstrate getting the date from a timestamp.
I suggest practicing the examples discussed below. It will surely improve your knowledge of various functions in Excel.
If you have your own spreadsheet ready with timestamps data, skip downloading the above file.
METHOD #1 – Convert timestamp to date using the DATE function
The DATE function should be coupled with the YEAR, MONTH, and DAY functions to extract the date from the timestamp.
All of these functions are pretty straightforward. Let’s understand them in the following section, and then we will jump to the steps to get the date from the timestamp.
Let’s begin,
Explained: The DATE Function in Excel
The DATE function in Excel returns a serial number corresponding to the year, month, and day values specified by the users.
It is one of the most essential data functions that must be understood.
The general syntax for the DATE function is as follows,
=DATE(year,month,day)Each argument of the above function is self-explanatory. Consider the date 15th February, 2023. If we put it in the DATE function, the formula would become,
=DATE(2023,02,15)Where,
- The “year” argument is replaced by “2023”
- The “month” argument is replaced by “02”
- The “day” argument is replaced by “15”
Explained: The YEAR function in Excel
The YEAR is a simple function that returns a four-digit year number from a given date. The general syntax for the function is as follows,
=YEAR(serial_number)Where you need to replace the “serial_number” argument with the given date.
Considering the previous date, 15th February 2023. If we put it in the YEAR function, the formula would become,
=YEAR(02-15-2023)Explained: The MONTH Function in Excel
Similar to the YEAR function, the job of the MONTH function is to return a two-digit month number from the given date.
The syntax of the MONTH function is as follows,
=MONTH(serial_number)Where the argument serial_number needs to be replaced with the date from which you wish to extract the month, note that you can either enter the month value manually or provide the cell reference holding the date.
Explained: The DAY Function in Excel
If you wish to get the day of the week from the given date, the DAY function is your way to go. It returns a two-digit day number from the given date value.
The general syntax for the function is as follows,
=DATE(serial_number)Here, the serial_number argument also needs to be replaced with the date from which you wish to extract the day.
Now, you know how to use the YEAR, MONTH, DAY, and DATE functions in Excel. Let’s use the knowledge to extract the date from the timestamp.
Note that first, you need to extract the year, month, and day from a timestamp and then use the DATE function to construct a date.
Here is an example dataset that includes student names and their birthdates. As you may have noticed, the birth dates in column B include date and time values.
Our task is to extract the date from column B, which contains the timestamps.
Here are the steps,
- Open the desired Excel spreadsheet
- Add a new column as shown below,
- Select the cell “C2”, which is the first cell of the newly inserted column
- Type “=DATE”
- Select the first option from the popup or press “Tab” on your keyboard
- Now, type “YEAR” and select the first available option from the popup as shown
- Enter the cell reference containing the date as “B2” in place of the “serial_number” argument
- Finish off the YEAR formula with closing brackets “)”
- Press “,” to move to the next argument of the DATE function
- Type “MONTH” and select the first available option from the popup, as shown below
- Enter the cell reference as “B2” and close the brackets using “)” on your keyboard
- Press “,” to move to the next argument of the DATE function
- Type “DAY” and choose the first option from the popup. Refer to the following image,
- Again, select the cell reference as “B2”, as it contains the date from which you wish to extract the day
- End the DAY formula with a closing bracket “)”
- Next, end the DATE formula with a closing parenthesis “)”
- Press the “Enter” button on your keyboard to see the result
Here’s how the final formula should look after following the above steps,
=DATE(YEAR(B2),MONTH(B2),DAY(B2))To apply this formula for the entire column, you can hover to the lower-rightmost corner of the first cell until you see the “+” icon. You need to click on that “+” icon and drag the formula to the end of the table.
METHOD #2 – Extract the date from a timestamp using the TEXT function
This method is quicker and easier than the first one discussed above. If you don’t wish to work your way around multiple functions, this method is your perfect choice.
Before we jump into the actual steps, let us first understand the TEXT function.
Explained: The TEXT Function in Excel
The TEXT function is used to convert numbers to text strings. It is one of the most used functions in Excel.
The general syntax for the function is as follows,
=TEXT(value, format_text)Each argument of the above function needs to be replaced with the proper information. Here’s how to do it,
- “value” – This argument represents the numerical value from which you wish to extract the text string
- “format_text” – Here, you can define the required text string format
The function is pretty straightforward, but you must know the proper text format that will return the desired output.
Let us consider the previous dataset, which includes full names and birth dates. Our task is to extract the date from the given timestamps in column B.
Let’s begin,
- Open your desired spreadsheet
- Add a new column as shown below
- Select the cell “C2”
- Type “=TEXT”
- Select the first option from the popup or press “Tab” on your keyboard
- Select the cell reference as B2 in place of the “value” argument of the TEXT function
- Press “,” to move to the next argument
- Now, enter the format in which you wish the date to be displayed. Here, I have entered “dd/mm/yyyy” in place of the “format_text” argument
(Make sure to enter the format in double quotations “ “) - End the formula with a closing parenthesis “)”
- Press the “Enter” button on your keyboard to see the result
Here’s how the final formula should look after following the above steps,
=TEXT(B2,"dd/mm/yyyy")METHOD #3 – By changing the cell format
You can format a cell to display only the date part of a timestamp without altering the underlying value.
This method gives you a visual solution to the problem. If you wish to use the value in other formulas in future, it may return incorrect results due to the presence of time values hidden by the applied cell formatting.
Here are the steps to change the cell formatting to get the date from the timestamp,
- Open your desired spreadsheet
- Select the timestamps in the column B
- Click on the “Home” tab of the main menu
- Go to the Number formatting section and click on the dropdown as shown below
- Next, click on the last option named “More Number Formats…”
- A new dialogue box will appear on your screen titled “Format Cells”
- Go to the “Number” tab
- Choose the “Custom” option from the list, as shown in the following image,
- In the “Type” field, choose the “dd/mm/yyyy” format
- Click the “OK” button
Excel will instantly change the formatting and display the dates corresponding to the given timestamps, as shown in the above GIF.
This method is quick but not useful if you wish to perform further calculations on the dataset.
Method #4 – By using the Text to Columns feature
Last but not least, this is my favorite method to extract the date from the timestamp. It works like a charm. It is as effective and useful as the first two methods discussed in this article.
It splits the contents of a cell into separate columns based on a specified delimiter, such as a space or a comma.
Here’s how you can use this method to separate the date from the time in a timestamp.
- Open the desired Excel spreadsheet
- Select the cells containing the timestamps
- Go to the “Data” tab
- Click on the “Text to Columns” option from the list
- A new dialogue box will appear on your screen, as shown below,
- Make sure the radio button before the option named “Delimited” is ticked
- Click the “Next” button
- Since timestamps have a space separating the date and time, check the “Space” checkbox. You can also deselect any other delimiters that are not essential
- Click on “Next” to continue to the next step
- Choose the destination cell where you want the split data to appear. By default, Excel will place the split data starting in the same column
- Click the “Finish” button to close the popup
The date and time parts of the timestamps will be split into separate columns. Refer to the above GIF.
As you can see, the date will be in one column, and the time will be in the next column.