Excel Split Date Time Into Columns For Date And Then Time Episode – 2666

Microsoft Excel Tutorial: Split Date Time into Columns for Date and then Time.

In this video, we tackle an intriguing Excel challenge from a real-world job interview scenario. Imagine being asked to split a column of date and time into separate date and time fields, build a pivot table, and create a report limited to certain conditions—class equals 1 and days are Tuesday or Thursday. Sounds tricky, right? Let’s break it down step by step and explore multiple approaches!

First, we’ll discuss how to split the date and time using simple formulas like =INT(A2) for the date and =A2-D2 for the time, along with proper formatting tips. Then, we’ll dive into creating a pivot table that reports by date instead of date and time. We’ll even explore grouping data by days and removing time elements directly within the pivot table.

But wait—what if Power Query is the better tool for this? We’ll walk through how to use Power Query to transform the data, removing the time component seamlessly, and setting up a new pivot table. Power Query is a game-changer for cleaning and reshaping data!

When it comes to filtering by weekday, we’ll show a smart trick using the =TEXT() function to extract the day name directly from the date, making it easier to filter for Tuesdays and Thursdays. Finally, we’ll combine it all with slicers to create an interactive, professional report.

Do you have a favorite method for these tasks? Would you handle this job interview challenge differently? Share your thoughts in the comments below! Don’t forget to like, subscribe, and hit the bell icon for more Excel tips and tricks from MrExcel!

Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/

Table of Contents
(0:00) Three interview questions about Excel Pivot Tables
(0:40) Excel stores date before decimal and time after decimal
(1:01) Formulas to split date and time in Excel
(1:55) Grouping Date/Time field to just Date in Pivot Table
(2:40) Excel Power Query to convert Date/Time to Date for pivot table
(3:21) Converting Date to Weekday using TEXT function in Excel
(4:40) Slicers to filter a pivot table in Excel
(5:10) How would you solve this?

This video answers these questions:
Split date and time in Excel
Excel pivot table by date only
Remove time from date in Excel
Excel formulas for date and time
Create pivot table with weekday filter
Power Query to separate date and time
Filter Excel pivot table by weekday
Use TEXT function for weekday in Excel
Excel job interview pivot table test
Advanced Excel pivot table tricks