Extract date from multiple cells containing both date and time

As if you have got a cell containing date along with the time in the same cell and you want to extract the date into a separate cell, then for one or two cells, you can extract the date manually from the cell, but if you have got multiple cells in excel containing both date and time in the same cell then doing it manually would be very tough you would get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting date from the multiple cells containing both date and time would become a piece of cake for you, as it is done in the following screenshot, So let’s dive into it:

General Formula:


The Following formula would help you out for extracting date from the multiple cells containing both date and time to another cell; in the above example, the formula is shown in the cell B2:

=INT(date_value)

Let’s See How This Formula Works:


You would definitely wonder that how excel efficiently and precisely extracts date from the multiple cells containing both date and time? Then here is the answer that the excel handles it by several numbers and fractional values, which means that Excel uses a pattern in which it denotes the dates as the several numbers and time to the fractional values.

For instance, suppose that both date and time  Feb 2, 2022, 19:00 PM are in the same cell, as now in Ms excel this date and time are represented in terms of numbers as “44594.7916666667, where the “44594” is the date portion, and the fractional part “.7916666667” is the time portion.

So if you want to extract date from the cells that include both the date and time, you can use the INT function to extract only the date part. The INT function returns only the decimal portion from each cell by excluding the fractional part, and you would get the date from each cell efficiently and precisely.

As by the aid of an example, you would understand it more clearly, so let suppose in A2 there are both date and time which is Feb 2, 2022, 19:00 PM, which is equivalent to the number 44594.7916666667 in MS Excel, so when you would use the INT function which is given below then it would return the date portion (44594):

=INT(A2)

extract date from date and time 1

Obviously, the fractional part or the time portion is discarded to get the date portion, but for that, be sure you are using a date format that does not include a time. Otherwise, according to the above example, you will see the time displayed as 19:00 PM instead of the date portion.

Related Functions


  • Excel INT function
    The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…

 

Related Posts

Calculate Number of Hours between Two Times

Calculating the difference between two times might be a valuable statistic for subsequent computations or averages, whether you're producing a time sheet for staff or recording personal exercises. While Excel has a plethora of complex functions, including date and time ...

Calculating Average Of The Numbers

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...

How to Stack Data from Multiple Columns into One Column in Excel
Stack Data 12

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...

How to Calculate Retirement Date and Remaining Years refer to Birth Date in Excel
Calculate Retirement Date 10

Every company has its own police about the retirement date for employees. Suppose employees will be retired from a certain age of 60, how can we calculate the retirement date for everyone base on their birthdays? Actually, there are several ...

How to List Only Whole Numbers From Decimal Numbers in Excel
list only whole numbers5

This post will guide you how to filter only whole numbers from decimal numbers in a list in Excel. How do I filter cells with whole numbers or non-whole numbers in Excel. List Only Whole Numbers Assuming that you have ...

How to Calculate Number of Weekends between Two Dates in Excel
calculate number of weekdays3

This post will guide you how to count the number of weekends between two given dates in Excel. How to find out how many weekends are between two dates in Excel. How to count the number of Sundays between two ...

How to Check If a Number is Integer in Excel
check if number is integer1

This post will guide you how to check if a number is integer in Cells in Excel. How do I test if Cell value is integer with a formula in Excel. Check If Value is Integer Assuming that you have ...

How to Delete Numbers after Decimal Point in Excel
delete numbers after decimal point2

This post will guide you how to remove decimals without rounding in Excel. How do I delete numbers after decimal point with a formula in the given cell in Excel. Deleting Numbers after Decimal Point Assuming that you have a ...

Count the Number of Weekends between Two Date
count number of weekend between two dates2

This post will guide you how to count the number of weekends between two dates in Excel. How do I get the number of weekends between 2 dates with a formula in Excel. How to count weekend days between dates ...

Sidebar