How To Remove Date From Time In Excel

To extract date from timestamp in Google Sheets is useful if you have DateTime values in your sheet, and you would like only to use their date value.

If a cell contains a combined date and fourth dimension, y'all might want to extract merely the engagement value. In this postal service, we will become through all the possible methods to cut off the time value from a timestamp.

You will encounter why is information technology incorrect merely to format the cell and how can you actually extract appointment from timestamp using the TO_DATE function together with INT or DATEVALUE.

Y'all volition also see how to apply the SPLIT function to split the timestamp into date and time.

Let's jump right in.

The Timestamp and DateTime in Google Sheets

When you work with information, you may desire to enter the current date or time near an item. This is chosen timestamping.

There are multiple ways to add timestamps in Google Sheets, and nosotros will talk over them below.

The timestamps are DateTime values. Information technology is a blazon of value in Google Sheets, amidst others like number, text, boolean, Date, or Time.

DateTime is a combined value storing both date and time. Depending on your regional settings, they tin take a different format than in the examples, but it always includes the following details: year, month, mean solar day, 60 minutes, minute, and seconds.

⚠️ How to Add together Timestamps in Google Sheets

You tin add the engagement and time manually. In this case, you accept to be enlightened of the locale settings, because every region has its own pattern for displaying engagement and time.
You can use a keyboard shortcut to add a timestamp: Ctrl+Alt+Shift+; (semicolon) to add the electric current date and time.
You can apply a formula: =Now() which returns the current date and fourth dimension to a cell. It is essential that this formula exist recalculated, and the result be renewed with every change fabricated in the table.
Or you lot tin add timestamps automatically using a script.

There might be unlike reasons for working with existing timestamps in your sheets. Normally, nosotros insert a timestamp in Google Sheets to mark when an activity (create, update, delete) was done.

When we desire to piece of work with these values, they are not always in the right format for our calculations.

Say you need just the date of a timestamp and non the time part because you lot would like to calculate something with the days of the timestamps.

In some cases, information technology might be enough to simply change the format of the jail cell by choosing Format > Number > Date.

Extract Date From Timestamp in Google Sheets

But be conscientious!

As you can run across, the result on the right (higher up epitome): only the day of the timestamp appears in the prison cell. However, information technology doesn't hateful that the time (hours, minutes, seconds) has been cut off from the timestamp.

It still exists just that it is non visible in the cell anymore.

Let's prove information technology! We accept 2 unlike timestamps on the same day. So nosotros format the values merely to make the date visible. They wait identical now, simply are these two cells really identical?

We are checking information technology with a unproblematic comparison using the EQ (equality) office.

          =EQ(C4, C5)

The equality role returns Imitation, significant that the two cells do NOT have the aforementioned value.

How to Extract Date from Timestamp in Google Sheets

Therefore, if you apply these cells in other calculations, the values they stand for are still the original values with the time details. Whatsoever comparison based on days volition fail because the subconscious part of the timestamps even so remains.

Anytime yous need your values to contain merely the date of your timestamp and not the fourth dimension, you will need to excerpt the engagement from the timestamp.

In the next part, we will show some methods to do that.

Y'all may brand a re-create of the spreadsheet using the link attached below and try it for yourself:

There are unlike ways to make clean the time from timestamps and merely keep the date. Let's go over each one:

Method one: Convert DateTime to Date using TO_DATE

An easy way to convert the DateTime to Date is past using the TO_DATE office.

The TO_DATE role converts a provided number to date. Information technology accepts one jail cell with a number to be converted to a date.

We don't accept a number in our timestamp, but a DateTime, so what can nosotros do?

Information technology is adept to know that Google Sheets treats every Date and DateTime as numbers in the groundwork. The date is stored as an integer, and the fourth dimension is stored as a decimal fraction.

Yous tin easily bank check how information technology works past formatting whatsoever of your Date or DateTime cells to numbers. For example, our first timestamp from the case is stored equally the number 43912.420775463.

When we would like to convert our DateTime to a Date using the TO_DATE function, we need to convert a not-integer to an integer in the groundwork.

In that location are two different means to do this.

1 way is to use the INT role that will simply cut off the decimal role of the number. We tin use this formula to extract date from timestamp using TO_DATE and INT functions:

          =TO_DATE(INT(A1))

Extract Date From Timestamp in Google Sheets

The other way is to employ the TO_DATE function together with the DATEVALUE part. The DATEVALUE office converts a cord to a engagement object. We treat our DateTime value as a cord and utilise this function to create a engagement from it. The formula with TO_DATE and DATEVALUE will look similar to the previous solution:

          =TO_DATE(DATEVALUE(A1))

Extract Date from Timestamp Using TO_DATE in Google Sheets

Method 2: Split the timestamp into date and time using SPLIT

The 2d way is a bit more sophisticated than the previous ane, and it is a different approach. The Split up function splits the date and time into two carve up cells.

It is useful if we desire to use both the appointment and the time.

The SPLIT function divides text around a specified character or cord and puts each fragment into a separate cell in the row.

Permit's wait at the syntax of the Dissever function:

          =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Let's break this downwardly and acquire what each of these terms means:

= the equal sign ishow we start every role in Google Sheets.
SPLIT this is our office. Nosotros volition have to add the corresponding value(southward) into it for it to piece of work.
text is the text to separate or the prison cell with the text to separate.
delimiter is the character or characters to utilize to split text.
split_by_each is an optional argument and it is Truthful past default. Past default, each character of the delimiter is considered individually, eastward.m., if the delimiter is "the", then the text is divided around the characters "t", "h", and "due east". Set split_by_each to Fake to plough off this behavior.
remove_empty_text is an optional argument, True past default. It describes whether or not to remove empty results from the split results. The default behavior is to care for consecutive delimiters equally one (if Truthful). If Fake, empty cell values are added between consecutive delimiters.

In our case, the text will be the cell containing the timestamp.

To divide our timestamp into date and time, nosotros demand to specify the second statement which is the delimiter. We need to find the grapheme(s) that split up the 2 parts of the timestamp. It is a white infinite (bare) character, so this is what we are going to use as a delimiter.

We are not adding the last two optional parameters of the SPLIT part, because we don't want to change their default values.

So this is the formula to split the timestamp into engagement and time:

          =Separate(A1," ")

The result is 2 separated cells, ane with the date and some other one with the fourth dimension. Using the prison cell with the date, nosotros have an extracted date from timestamp, which was our main goal.

Extract Date from Timestamp Using SPLIT in Google Sheets

That's pretty much information technology. Y'all can now extract date from timestamp in Google Sheets. Try experimenting and integrating the other numerous Google Sheets formulas, to create fifty-fifty more powerful formulas that can make your life much easier. 🙂