In today's podcast, we take a look at breaking Romas' date/time column into a date column using the INT function. Episode 752 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Yesterday, we talked about this data set.
It has date and time in column A and energy usage in column B.
We wanted to summarize that by day and, yesterday, I used a pivot table in Excel 2007.
Now, you know, most of the time, I'm doing the podcast in 2003.
This doesn't work in 2003 because I have so many items.
35,000 items is more than the limit.
Back in Excel 2003, we couldn't have more than 32,000 items in any one field of a pivot table.
So, what I'm going to do here is I'm going to insert a new column and I'm going to call it the DATE column.
Now, if our dates over there in column A are truly stored as date and time, then it's going to be very simple.
We’re going to use the =INT function.
INT is basically I’m going to chop off the time portion of that.
So, we can format that cell, choose, for example, just the first date format, copy it down, and we've actually now converted those dates and times into very simple days.
We’ll allow that to calculate, copy and paste special values, and then it's just a matter of building a little summary table over here.
So, 9/1/2006, we'll grab the fill handle and copy this down.
I think our data goes somewhere into 2008, far enough, and we can use the SUMIF.
=SUMIF.
We'll take a look at this range here in column B.
If it's = to that particular day, then we want to sum up the corresponding cell from the range in column C, copy that down, and we'll have our summary table.
[ =SUMIF($B$2:$B$35000,F3,$C$2:$C$35000) ] Now, this becomes more difficult if that data over there in column A is not actually stored as [ unintelligible – 01:52 ] but instead is stored is text.
We'll take a look at how to solve that in tomorrow's podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Yesterday, we talked about this data set.
It has date and time in column A and energy usage in column B.
We wanted to summarize that by day and, yesterday, I used a pivot table in Excel 2007.
Now, you know, most of the time, I'm doing the podcast in 2003.
This doesn't work in 2003 because I have so many items.
35,000 items is more than the limit.
Back in Excel 2003, we couldn't have more than 32,000 items in any one field of a pivot table.
So, what I'm going to do here is I'm going to insert a new column and I'm going to call it the DATE column.
Now, if our dates over there in column A are truly stored as date and time, then it's going to be very simple.
We’re going to use the =INT function.
INT is basically I’m going to chop off the time portion of that.
So, we can format that cell, choose, for example, just the first date format, copy it down, and we've actually now converted those dates and times into very simple days.
We’ll allow that to calculate, copy and paste special values, and then it's just a matter of building a little summary table over here.
So, 9/1/2006, we'll grab the fill handle and copy this down.
I think our data goes somewhere into 2008, far enough, and we can use the SUMIF.
=SUMIF.
We'll take a look at this range here in column B.
If it's = to that particular day, then we want to sum up the corresponding cell from the range in column C, copy that down, and we'll have our summary table.
[ =SUMIF($B$2:$B$35000,F3,$C$2:$C$35000) ] Now, this becomes more difficult if that data over there in column A is not actually stored as [ unintelligible – 01:52 ] but instead is stored is text.
We'll take a look at how to solve that in tomorrow's podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
