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

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

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

 

Comments

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

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

Leave a Reply to Prakash Cancel reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.