How to get the Separating Dates and Times in Microsoft Excel

In this article, you’ll learn how to separate dates and time from a cell you need to use the INT & MOD formulae in Microsoft Excel.

For data analysis we have to extract several data from software. From few software’s we get standardize data in which we get dates along with time. We should know how to separate dates and time.

Let’s take an example and understand:-

To separate date and time follow below given steps:-

Select the cell B1 and write the formula to extract date from cell A1
=INT(A1), press enter
Format this cell as “mm/dd/yyyy”
To format the cell, press the key “CTRL+1”
Format Cells dialog box will appear

In the “Number” tab select “Custom” in the type box, type the date format as DD/MM/YYYY

Click on OK

To separate time follow the below given steps:-

Select the cell B1 and write the formula to extract time from cell A1
=MOD (A1, 1), press Enter

Format this cell as “h:mm”
To format the cell, press the key “CTRL+1”
The “Format Cells” dialog box will appear
In the “Number” tab select “Custom” in the type box, type the date format as “h:mm”

Click OK

In this way, we can separate date and time from a cell in Microsoft Excel by using the functions.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel

Amit
April 30, 2015 at 4:37 pm

If I have a date and time format like following than how this will work. Is there any way to do so:

January 1, 1900 12:00:00 AM

Reply ↓
- Prakash
  January 9, 2020 at 4:05 am
  
  =TIME(HOUR(A1),MINUTE(A1), SECOND(A1))
  
  A1 is the cell where you have your data - January 1, 1900 12:00:00 AM.
  
  Reply ↓
Jeannie
November 25, 2014 at 11:17 am

Is there a formula that I can use to group a set of "time".
For example:
12:01 PM
12:15 PM
1:05 PM
2:18 PM
2:20 PM
1:08 PM

Then I want to group them into these intervals
12:00PM-12:30PM
12:31PM-1:00PM
1:01PM-1:30PM
1:29PM-2:00PM
2:01PM-2:30PM

Hope you can help me with this. Thanks in advance!

Reply ↓
- Eugene Ng
  February 28, 2020 at 3:35 pm
  
  Use pivot tables
  
  Reply ↓