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: MrExcel 2024 Igniting Excel
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
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: MrExcel 2024 Igniting Excel
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
Transcript of the video:
Oh, I've got a good one today. Alright, we have a column that has date.
And time and we want to split that into date and then time.
And I knew that this was going to be simple, but now I'm second guessing myself.
A neighbor of mine went on a job interview. The hiring manager loves pivot tables.
They want to make sure this person can do pivot tables.
They had them do a hands-on test. Can you build a pivot table?
How to report by date instead of date and time?
And then limit the report to class equals one and the day is Tuesday or Thursday. That makes it really hard.
Okay, so my initial reaction was we have to attack number two first, right?
And I showed the person how this date and time…. If we go into Show Formulas mode.
Is really a serial number date.
The number of days elapsed since January 1st, 1900.
And then the time is the fraction of the day after that decimal point. And so I said it's two weird formulas.
To get the date, we're going to take A2 and we're going to truncate it or take the INT of it.
So equal INT of A2. And that may not be formatted correctly.
So we have to come up here and change it to a Short Date.
And then to get the time, well actually, now that we already have the date, it's just simple.
It's A2 minus D2. Definitely it won't be formatted correctly.
But we can come in here and format that as a time.
And so now we have it broken out that the date is there and the time is there. And now we're free to go create that pivot table.
But then it's just weird that they asked for question one, can you build a pivot table?
And then how to report by date instead of date and time.
And now I'm starting to wonder if what they wanted the person to be able to do.
Would be to build the pivot table first. Insert, Pivot Table.
Go ahead and go to a new worksheet. Put the dates down the left hand side.
And they're going to show up as date and time.
And then whatever we're going to put in the values area.
And then we can with the first date or that heading selected, either one.
Come here and Group Field. And say we want to group it to just days.
That's a very clever way to take that time and just remove it, right?
So if we click okay, then we get a new field called Days Date.
Which is just the dates. And the times are gone.
What do you think? Is that what they were looking for?
Or were they trying to do some power query? Where we take this data.
Make it into a table with control T. And then Data, from Table or Range.
And then in Power Query. We can come here under Transform.
And say that it's just a straight date that will remove the times.
And now we're free to create our pivot table. Home, Close and Load, Close and Load To.
And we're going to create a pivot table report.
And then the date field will automatically be the date. So there's a couple of different ways to go.
And I guess if it wasn't for question number three.
Llimit the report to class one and Day is Tuesday or Thursday.
See that Tuesday or Thursday? That makes this a real pain, right?
Because then I need to somehow get the weekday out of this.
And I know we have the WEEKDAY function.
But I actually hate using WEEKDAY because I can never. Because I can never remember what the returns are.
It's like one is Sunday. So later, am I ever going to remember that?
That means that two is Monday. Three is Tuesday.
So I need the threes and the fives to get the Tuesdays and Thursdays.
This is just too confusing to me.
So what I always do is I use the equal TEXT function of that date. And I ask for it in DDDD format.
You could also use three Ds there if you wanted the abbreviation.
And that will give you the day of the week.
Alright, see now that I have date, time, and weekday. Now if we create a pivot table.
Insert, Pivot Table. Click okay.
We can put the totals here in values. We can, put the dates down the side.
Classes across the top. And then add a slicer in.
And then add a Slicer for a weekday and class. Click okay.
And we'll choose Tuesday. And then control click on Thursday.
That'll get just the Tuesday and Thursday dates. And then what was the class?
Class is equal to one. So just one there.
And so there's our answer of the 9,064. Yeah.
So what do you think? For all my regular viewers out there.
If you were heading into an interview. And you've got these questions.
How do you think that they are trying to limit the report by date instead of date and time?
Do you think that they're trying to see if you know these formulas to get the integer of A2?
Or do you have a better way to get the date? And then for reporting by weekday.
Or at least getting just the Tuesdays and Thursdays.
Would you use the TEXT function here or something else?
Let me know down in the YouTube comments. Well hay, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
And time and we want to split that into date and then time.
And I knew that this was going to be simple, but now I'm second guessing myself.
A neighbor of mine went on a job interview. The hiring manager loves pivot tables.
They want to make sure this person can do pivot tables.
They had them do a hands-on test. Can you build a pivot table?
How to report by date instead of date and time?
And then limit the report to class equals one and the day is Tuesday or Thursday. That makes it really hard.
Okay, so my initial reaction was we have to attack number two first, right?
And I showed the person how this date and time…. If we go into Show Formulas mode.
Is really a serial number date.
The number of days elapsed since January 1st, 1900.
And then the time is the fraction of the day after that decimal point. And so I said it's two weird formulas.
To get the date, we're going to take A2 and we're going to truncate it or take the INT of it.
So equal INT of A2. And that may not be formatted correctly.
So we have to come up here and change it to a Short Date.
And then to get the time, well actually, now that we already have the date, it's just simple.
It's A2 minus D2. Definitely it won't be formatted correctly.
But we can come in here and format that as a time.
And so now we have it broken out that the date is there and the time is there. And now we're free to go create that pivot table.
But then it's just weird that they asked for question one, can you build a pivot table?
And then how to report by date instead of date and time.
And now I'm starting to wonder if what they wanted the person to be able to do.
Would be to build the pivot table first. Insert, Pivot Table.
Go ahead and go to a new worksheet. Put the dates down the left hand side.
And they're going to show up as date and time.
And then whatever we're going to put in the values area.
And then we can with the first date or that heading selected, either one.
Come here and Group Field. And say we want to group it to just days.
That's a very clever way to take that time and just remove it, right?
So if we click okay, then we get a new field called Days Date.
Which is just the dates. And the times are gone.
What do you think? Is that what they were looking for?
Or were they trying to do some power query? Where we take this data.
Make it into a table with control T. And then Data, from Table or Range.
And then in Power Query. We can come here under Transform.
And say that it's just a straight date that will remove the times.
And now we're free to create our pivot table. Home, Close and Load, Close and Load To.
And we're going to create a pivot table report.
And then the date field will automatically be the date. So there's a couple of different ways to go.
And I guess if it wasn't for question number three.
Llimit the report to class one and Day is Tuesday or Thursday.
See that Tuesday or Thursday? That makes this a real pain, right?
Because then I need to somehow get the weekday out of this.
And I know we have the WEEKDAY function.
But I actually hate using WEEKDAY because I can never. Because I can never remember what the returns are.
It's like one is Sunday. So later, am I ever going to remember that?
That means that two is Monday. Three is Tuesday.
So I need the threes and the fives to get the Tuesdays and Thursdays.
This is just too confusing to me.
So what I always do is I use the equal TEXT function of that date. And I ask for it in DDDD format.
You could also use three Ds there if you wanted the abbreviation.
And that will give you the day of the week.
Alright, see now that I have date, time, and weekday. Now if we create a pivot table.
Insert, Pivot Table. Click okay.
We can put the totals here in values. We can, put the dates down the side.
Classes across the top. And then add a slicer in.
And then add a Slicer for a weekday and class. Click okay.
And we'll choose Tuesday. And then control click on Thursday.
That'll get just the Tuesday and Thursday dates. And then what was the class?
Class is equal to one. So just one there.
And so there's our answer of the 9,064. Yeah.
So what do you think? For all my regular viewers out there.
If you were heading into an interview. And you've got these questions.
How do you think that they are trying to limit the report by date instead of date and time?
Do you think that they're trying to see if you know these formulas to get the integer of A2?
Or do you have a better way to get the date? And then for reporting by weekday.
Or at least getting just the Tuesdays and Thursdays.
Would you use the TEXT function here or something else?
Let me know down in the YouTube comments. Well hay, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
