2 Methods to Separate Date and Time in Excel

Posted on:  08/30/2024
2 Methods to Separate Date and Time in Excel

Introduction

Welcome to our guide on how to separate date and time in Excel. In this blog, we will explore two dynamic methods to achieve this: using the Integer function and employing Power Query. These methods are perfect for analyzing data trends such as popular class times or customer purchase times.

Let's dive in and learn how to separate date and time in Excel efficiently.

Using the Integer Function

The Integer function method is a straightforward way to separate date and time in Excel. This method keeps your data dynamic, meaning any changes in the original data will automatically update the separated date and time.

YouTube Video - 2 Methods to Separate DATE and TIME in Excel

Step-by-Step Guide

First, let's look at how to use the Integer function to separate the date and time from a single field.

  1. In column B, you have the class Date and Time listed. To extract the date, you will use the Integer function. In column E, type =INT(B2) and press Enter.
  2. The Integer function will give you the date. To format it properly, highlight the column, go to the Home tab, and select Short Date in the Number group. This ensures the dates are displayed correctly.
  3. To extract the time, use the formula =B2-INT(B2) in column F. This will leave you with just the time.
  4. Highlight column F and format it as Time in the Number group. Now, you have the date and time separated.
INT Function to separate the date and time

Excel INT Function to separate date and time - get the DATE

Popular Excel Articles

Excel INT Function to separate date and time

Excel INT Function to separate date and time - get the TIME

Popular Copilot in Excel Articles

Excel Short Date and Time when using INT Function

Excel Short Date and Time when using INT Function

Analyzing Data with PivotTable

One of the main reasons to separate date and time is to analyze data trends. In our example, we want to determine the most popular time for training classes. You could also use Time to find out when most customers place orders, or any time related field.

  1. Select your data and insert a PivotTable. Drag the Time field to the Rows area and the Class Date to the Values area. This will give you a count of classes at different times.
  2. The PivotTable shows that 10 AM is the most popular time, with 62 people signing up for classes.
PivotTable showing the preferred time

PivotTable showing the preferred time

Popular Copilot Articles

Using Power Query

Power Query is another excellent method to separate date and time in Excel. This method is highly dynamic and allows for easy data refreshes, making it ideal for large datasets.

Step-by-Step Guide

Let's explore how to use Power Query to separate date and time.

1. Click on your data and go to the Data tab. Select From Table/Range under the Get and Transform Data group. Power Query will launch.

Launching Power Query from Data tab

2. In Power Query, select the column with the class date. Go to the Add Column tab and choose Date Only from the Date & Time group. This will extract the date.

Using Power Query to extract date

3. Repeat the process to extract the time. Select the column again, go to the Add Column tab, and choose Time Only from the Date & Time group.

Using Power Query to extract time

4. Once you have separated the date and time, go to the Home tab and select Close & Load To. Choose to load the data into a new table in Excel.

Loading data back into Excel from Power Query

Refreshing Data with Power Query

One significant advantage of using Power Query is the ability to refresh data easily. If your original data changes, you can quickly update the separated date and time.

1. After adding new records to your original data, go to the Queries and Connections pane in Excel. Right-click on your query and select Refresh.

Refreshing data in Power Query

2. The data will update, reflecting any changes made in the original dataset.

Updated data after refresh in Power Query

Conclusion

Separating date and time in Excel can be efficiently done using the Integer function or Power Query. Both methods have their advantages, with Power Query offering more dynamic capabilities and easier data refreshes.

We hope you found this guide helpful. Feel free to subscribe to our channel for more Excel tips and tricks. Let us know in the comments which method you prefer: Integer function or Power Query.

Thank you for reading!

Recent Articles 

Mastering Loan Amortization: A Comprehensive Guide to Understanding and Comparing Loans

Mastering Loan Amortization: A Comprehensive Guide to Understanding and Comparing Loans

Analyzing 50+ Years of Mortgage Rates in Excel: Insights and Trends

Analyzing 50+ Years of Mortgage Rates in Excel: Insights and Trends

Social Security - Breakeven point in Years using Excel

Social Security - Breakeven point in Years using Excel

Creating a Column Chart using S&P 500 data with Positive and Negative Colors in Excel

Creating a Column Chart using S&P 500 data with Positive and Negative Colors in Excel

Outlook on the web - 3 Calendar features the desktop version doesn't have

Outlook on the web - 3 Calendar features the desktop version doesn't have

Outlook - Manage your Inbox using Ignore, Archive, Block, Delete, and Clean Up

Outlook - Manage your Inbox using Ignore, Archive, Block, Delete, and Clean Up

Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories