When Romas sent in the question for podcast 751, I couldn't tell from the screenshot if his data actually was date/time or text that looked like a date/time. In Episode 753, I take a look at how to tell if the cell contains a date or something that looks like a date. You will also see how to use the TEXT, LEFT, MID, DATE functions to convert the text date/time to a real date.
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.
Well, this is day 3 talking about this data set where we have DATE and TIME in column A.
Now, here’s another “got you” that you can watch out for.
Yesterday, we used the INT function in column B to separate the DATE portion from the DATE and the TIME.
Well, in this case I get a VALUE error, and let's hold down the CONTROL key and press the backwards accent.
In the US, that's on the same key as the ~, right underneath the F1, and we'll see show formulas mode, and we'll see over there in column A, that's just text.
It’s not really a date and time that's stored over there.
Now, we’ll contrast that to yesterday's data set.
If I would do the same thing with yesterday's data set, we actually have serial numbers stored over there.
It's a real date and time.
So, in this case, we have a couple of problems.
Well, the one thing we could do is just look at that and say, okay, the year, the month, the day, [ +2 periods – 01:03 ] is taking up 10 characters.
So, we could ask for =LEFT of this, , 10, and that will give us just the date portion, and, unfortunately, it's still giving us just a text representation of the day.
[ =LEFT(A2:10) ] Well, this is going to be a little bit more difficult because now, when we put in 9/1/2006 and we copy that down, we're going to have to add an intermediate formula over in column G that says =TEXT of that date using a format of YYYY.MM.DD, and that will take the real date in column F and convert it to a text date in column G, copy that down, and now it's the matter of using the same formula we used yesterday.
=SUMIF.
Take a look at all those dates in column B, see if they're = to this text date in column G.
If it is, add up the corresponding cell from column C, copy that down.
[ =TEXT(F3,”yyyy.mm.dd”) ], [ =SUMIF($B$2:$B$35000,G3,$C$2:$C$35000) ] Now, a completely different approach, if we didn't want to have to have this extra column G, is to write probably a more difficult formula back in column B.
This is using the DATE function, =DATE, and we need to pass it a year, a month, and a day.
Well, for the year, that's the leftmost 4 characters of cell A2, for the month, we’ll use MID and ask for the 6th for a length of 2, and then for the day, we'll use MID again, this time asking for the 9th character for a length of 2.
That will convert that text date over in column A to a true date.[ =DATE(LEFT(A2,4),MID(A2,6,4),MID(A2,9,2)) ] Now, when we format this, we’ll choose, in the DATE, maybe the first DATE format, copy that down to all of our cells -- I'll double click the fill handle -- and now we don't have to use this intermediate column G.
We can actually have this based on column F. [ =SUMIF($B$2:$B$35000,F3,$C$2:$C$35000) ] So, what started out a few days ago with a very, very simple question -- hey, how do I summarize this data by date -- there were a lot of land mines that caused some problems.
More than 32,000 rows, so we couldn't do a pivot table in Excel 2003.
Yesterday, we took a look at a simple formula that will be very easy if the data in column A truly is a serial number, date, and time, but, today, if we have text over there…and it's hard to tell unless you press show formulas, that's CONTROL and the `, to tell whether you have text or a date.
In this case, we had to jump through some hoops to convert those text dates to real dates.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, this is day 3 talking about this data set where we have DATE and TIME in column A.
Now, here’s another “got you” that you can watch out for.
Yesterday, we used the INT function in column B to separate the DATE portion from the DATE and the TIME.
Well, in this case I get a VALUE error, and let's hold down the CONTROL key and press the backwards accent.
In the US, that's on the same key as the ~, right underneath the F1, and we'll see show formulas mode, and we'll see over there in column A, that's just text.
It’s not really a date and time that's stored over there.
Now, we’ll contrast that to yesterday's data set.
If I would do the same thing with yesterday's data set, we actually have serial numbers stored over there.
It's a real date and time.
So, in this case, we have a couple of problems.
Well, the one thing we could do is just look at that and say, okay, the year, the month, the day, [ +2 periods – 01:03 ] is taking up 10 characters.
So, we could ask for =LEFT of this, , 10, and that will give us just the date portion, and, unfortunately, it's still giving us just a text representation of the day.
[ =LEFT(A2:10) ] Well, this is going to be a little bit more difficult because now, when we put in 9/1/2006 and we copy that down, we're going to have to add an intermediate formula over in column G that says =TEXT of that date using a format of YYYY.MM.DD, and that will take the real date in column F and convert it to a text date in column G, copy that down, and now it's the matter of using the same formula we used yesterday.
=SUMIF.
Take a look at all those dates in column B, see if they're = to this text date in column G.
If it is, add up the corresponding cell from column C, copy that down.
[ =TEXT(F3,”yyyy.mm.dd”) ], [ =SUMIF($B$2:$B$35000,G3,$C$2:$C$35000) ] Now, a completely different approach, if we didn't want to have to have this extra column G, is to write probably a more difficult formula back in column B.
This is using the DATE function, =DATE, and we need to pass it a year, a month, and a day.
Well, for the year, that's the leftmost 4 characters of cell A2, for the month, we’ll use MID and ask for the 6th for a length of 2, and then for the day, we'll use MID again, this time asking for the 9th character for a length of 2.
That will convert that text date over in column A to a true date.[ =DATE(LEFT(A2,4),MID(A2,6,4),MID(A2,9,2)) ] Now, when we format this, we’ll choose, in the DATE, maybe the first DATE format, copy that down to all of our cells -- I'll double click the fill handle -- and now we don't have to use this intermediate column G.
We can actually have this based on column F. [ =SUMIF($B$2:$B$35000,F3,$C$2:$C$35000) ] So, what started out a few days ago with a very, very simple question -- hey, how do I summarize this data by date -- there were a lot of land mines that caused some problems.
More than 32,000 rows, so we couldn't do a pivot table in Excel 2003.
Yesterday, we took a look at a simple formula that will be very easy if the data in column A truly is a serial number, date, and time, but, today, if we have text over there…and it's hard to tell unless you press show formulas, that's CONTROL and the `, to tell whether you have text or a date.
In this case, we had to jump through some hoops to convert those text dates to real dates.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
