{"id":32890,"date":"2023-11-15T21:44:09","date_gmt":"2023-11-15T20:44:09","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=32890"},"modified":"2024-06-05T18:43:19","modified_gmt":"2024-06-05T16:43:19","slug":"separate-date-and-time","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/separate-date-and-time\/","title":{"rendered":"How to Separate Date and Time in Excel"},"content":{"rendered":"

\"How<\/a><\/p>\n

There are situations when you need to split a date and time that are combined into a single cell or column. This could be done for various reasons, including:<\/p>\n

– Performing calculations or analysis on the date and time components separately.
\n– Formatting the date and time differently for presentation or reporting.
\n– Integrating date and time data into other applications or systems that require them to be separate.<\/p>\n

In this article, we will explore various methods to separate date and time in Excel –<\/p>\n

Download the Excel Workbook below to follow along and understand How to Separate Date and Time in Excel \u2013<\/h4>\n

download excel workbook<\/strong><\/span>SeparateDateandTime.xlsx<\/span><\/a><\/p>\n

Text to Columns<\/span><\/strong><\/h3>\n

Select the cell or column containing the combined<\/a> date and time data.<\/p>\n

\"How<\/a><\/p>\n

Go to the Data<\/strong> tab and click on Text to Columns<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Choose Delimited<\/strong> in the wizard then select Next<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Select the appropriate delimiter that separates date and time (e.g., space or a comma). In our case we will select Space<\/strong>, then click Next<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

From here, we need to decide where we want the split data times to appear. By default it will overwrite the existing values. But we can change it to C2<\/strong> instead.<\/p>\n

\"How<\/a><\/p>\n

Click Finish.<\/strong><\/p>\n

Click here<\/a> to learn how to Split First & Last Name Using Text to Columns!<\/p>\n

\"How<\/a><\/p>\n

LEFT and RIGHT Functions<\/span><\/strong><\/h3>\n

You can use the LEFT and RIGHT<\/strong> functions to extract specific portions of text data<\/a>. Assuming the date is on the left side of the combined data, you can use the LEFT function<\/a> to extract it to column B<\/strong>.<\/p>\n

In cell B2<\/strong>, enter =LEFT(A2,5)<\/strong>
\n(The number 5 represents the number of characters removed<\/a> from the right. Essentially, only leaving the date value)<\/p>\n

\"How<\/a><\/p>\n

As you can see the data comes out in a different code. Before we can change this into a date format<\/a>, we will drag the formula<\/strong> all the way down to the bottom of the column.<\/p>\n

\"How<\/a><\/p>\n

Highlight the cells<\/a> in column B, right-click<\/strong>, select Copy<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Right-click<\/strong> again, and select Paste by Value<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Click on the caution box<\/strong>, and select Convert to Numbers<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Right-click<\/strong> and select Format Cells<\/a><\/strong>. Select Date<\/strong>. Click OK<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

To extract the time, you can use RIGHT function. Assuming the time is on the right side of the combined data, you can use the RIGHT function to extract it to column C.<\/p>\n

In cell C2<\/strong>, enter =RIGHT(A2,12)<\/strong>
\n(The number 12 represents the number of characters removed from the right. Essentially, only leaving the time value)<\/p>\n

\"How<\/a><\/p>\n

As you can see the data comes out in a different code. Before we can change this into a date format, we will drag the formula<\/a><\/strong> all the way down to the bottom of the column.<\/p>\n

\"How<\/a><\/p>\n

Highlight<\/strong> the cells in column C, right-click<\/strong>, select Copy<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Right-click<\/strong> again, and select Paste by Value.<\/strong><\/p>\n

\"How<\/a><\/p>\n

Click on the caution box<\/strong>, and select Convert to Numbers<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Right-click and select Format Cells<\/strong>. Select Time<\/strong>. Click OK<\/strong>.<\/p>\n

Click here<\/a> to Unlock the Power of Excel: 8 Substring Functions for Easy Data Analysis<\/p>\n

\"How<\/a><\/p>\n

\"How<\/a><\/p>\n

INT and MOD Functions<\/span><\/strong><\/h3>\n

Let\u2019s say we have the date and time values in Column A. To get the date portion, we can use the INT function.<\/p>\n

In cell B2<\/strong>, enter =INT(A2)<\/strong><\/p>\n

\"How<\/a><\/p>\n

Double-click or click and drag the small square on the bottom-right corner of cell B2 to apply the formula to the rest of the column<\/a>.<\/p>\n

\"How<\/a><\/p>\n

(You may notice that the format is still showing the time as 00:00:00. To remove this, right-click and select Format Cell. Select Date.)<\/em><\/p>\n

To get the time element, we can use the MOD function. click on cell C2<\/strong>, and enter =MOD(A2,1)<\/strong><\/p>\n

\"How<\/a><\/p>\n

Double-click or click and drag the small square on the bottom-right corner of cell B2 to apply the formula to the rest of the column.<\/p>\n

\"How<\/a><\/p>\n

(The format will may show the date. Right-click and click on Format Cell. Choose Time.)<\/em><\/p>\n

Click here<\/a> to learn How to get Remainder in Excel using MOD Formula!<\/p>\n

By default, these formulas<\/a> will be linked to the original values. If you change the values in Column A, it will directly reflect the changes in Column B and C. The formula is dynamic.<\/p>\n

\"How<\/a><\/p>\n

If you want Columns B and C to be independent of Column A, highlight B2 to C7<\/strong>. Right-click and Copy<\/strong>. Right-click<\/strong> again, and select Paste Values<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

\"How<\/a><\/p>\n

Power Query<\/span><\/strong><\/h3>\n

Power Query is a powerful data transformation tool available in Excel<\/a>. It can be used to split and manipulate date and time data with ease. You can use the Split Column<\/a><\/strong> feature to divide the data based on specific criteria.<\/p>\n

Select Column A<\/strong><\/p>\n

Go to the Data<\/strong> tab and click on Get Data<\/strong> or Get & Transform Data<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Click on From Sheet.<\/strong><\/p>\n

Go to the Add Column<\/strong> tab. Click on Date<\/strong>, then Date Only<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

\"How<\/a><\/p>\n

Click back on the Original column<\/strong> and select Time<\/strong>. Click on Time Only<\/strong>.<\/p>\n

Click here<\/a> to learn how Split a Query into Two Parts in Power Query!<\/p>\n

\"How<\/a><\/p>\n

\"How<\/a><\/p>\n

Go back to the Home<\/strong> tab.<\/p>\n

Click the Close & Load<\/strong> button to apply the changes and load the data back into your Excel worksheet.<\/p>\n

\"How<\/a><\/p>\n

Conclusion<\/strong>:
\nThere you have it! Separating date and time in Excel<\/a> is a simple and useful skill when dealing with combined date-time data. Whether you use basic methods like Text to Columns and text functions or advanced techniques involving Power Query<\/a>, Excel offers a wide range of tools to help you effectively manage your date and time data.<\/p>\n

Click here<\/a> to learn how to use the INT and FIX functions in Excel!<\/p>\n","protected":false},"excerpt":{"rendered":"

There are situations when you need to split a date and time that are combined into a single cell or column. This could be done for various reasons, including: – Performing calculations or analysis on the date and time components separately. – Formatting the date and time differently for presentation or reporting. – Integrating date […]<\/p>\n","protected":false},"author":1,"featured_media":32930,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"How to Separate Date and Time in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,121,300,287],"tags":[1448,1327,1447,117,1328,1446],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/32890"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=32890"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/32890\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/32930"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=32890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=32890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=32890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}