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:-

image 1

 

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

image 2

 

  • Format this cell as “mm/dd/yyyy”
  • To format the cell, press the key “CTRL+1”
  • Format Cells dialog box will appear

image 3

 

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

image 4

 

  • Click on OK

image 5

 

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

image 6

 

  • 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”

image 7

 

  • Click OK

image 8

 

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

 

image 48
 

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

 
 



2 thoughts on “Separating Dates and Times in Microsoft Excel

  1. 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!

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>