Gagan has sales for three years. For each month in those three years, what is the first date that had a sale? For some reason, Gagan needs to do this without a helper column.
My first solution is a pivot table using MIN of Date in the value area.
Note: Excel is changing the label when you group daily dates in a pivot table.
Later, I show a formula using XLOOKUP and "Equal or Just Larger". both in a column or in conditional formatting.
But then, when I try to use MINIFS, I can not get it to work without a helper column, possibly due to the array of arrays problem.
My first solution is a pivot table using MIN of Date in the value area.
Note: Excel is changing the label when you group daily dates in a pivot table.
Later, I show a formula using XLOOKUP and "Equal or Just Larger". both in a column or in conditional formatting.
But then, when I try to use MINIFS, I can not get it to work without a helper column, possibly due to the array of arrays problem.
Transcript of the video:
This is a tough one.
We have a bunch of dates.
We need to figure out the date of the first sale in each month.
This question sent it on YouTube.
Several years and dates in that year.
For example, 2004, 2005, 2006.
There's dates in several months. I need to find the first date in each month, preferably without a helper column, which makes this tougher.
So here I have in E2 to E50, just a bunch of random dates.
I sorted over here just to check.
So, for example like in 7/23, that wasn't the first date of the month.
The first date that we had a sale in in July of 2004 was 7/13.
And so you see here I'm not marking this.
And before I even get into the formulas, I really think after working on this for awhile, a pivot table is the way to go.
So this pivot table: for each month down the left hand side.
Each year across the top.
Shows me the first date that we had that sale.
Let's create the pivot table first.
So the pivot table: we go Insert, PivotTable.
Put it here in an existing worksheet, click OK.
On the left hand side, I'm going to put Dates.
And then in the Values area I'm going to put Dates as well.
And it's going to give me a Count.
But I can double click on the heading there and say that I want to show this as a Min.
I will go in to Number Format and choose Date.
Click OK.
Click OK.
And it's showing me the earliest date for each date, which at this point I realize is silly.
Then we choose the one of the date fields in the Rows area.
Go to Group Field and say we want to roll that up to months and years.
Hey, this is really wild.
Check this out.
This just started happening last week.
This must be a new change.
They now show us that the month is based on the Date field and years is based on the Date field.
I remember a long time ago.
I had a situation where I had two different dates.
I had like a Forecast Date and an Invoice Date and when you rolled those up it was really confusing which is which.
This is kind of a nice nice improvement here that they've renamed that.
I think.
I don't know – it might drive me crazy.
Right now, it seems… it seems good.
Let me know what you think down in the YouTube comments.
Will take the years field and drag it over to columns.
And one more thing, in my pivot table defaults.
I've turned off For Empty Cells.
I always say show 0 instead of the default of blank.
So change that back right.
So now for each month we can see what the first date we had.
I don't know, this seems faster than any formulas.
So that's how I would do it.
But I'm curious here for my formula experts, my Lambda experts.
How you would approach this, from the point of view of a formula.
ou know what I came up with.
Is let's convert that date to the 1st of the month, so eight one 2006 and then do an XLOOKUP.
Looking for the value that's equal to or just greater - that 1 right there is giving us the value that's equal to or just greater.
And then if that's equal to the date.
So in the case of 8/9/2006, if we scroll down here that is the first date in August.
So that will be a match.
We get the word First.
Otherwise quote quote.
And then here under Conditional Formatting we have a rule that's very similar.
Doing the same XLOOKUP seeing if it's equal to the value and highlighting the dates.
Alright, so I'm marking the first date.
And that's all working pretty well without a helper column.
But then I thought, well, let's see if there's some way that we can extract the first date of the month.
So over here I'm looking for the MINIFS.
Look for all of those dates over in column B, and I use the TEXT function here to convert it to YYYYMM.
So check and see if the value here is equal to this current month.
In other words, for all the things that are in August of 2006, let's convert them to 200608, use the MINIFS to find the smallest date in 200608, and that's what we get here.
Now unfortunately, that gives us some duplicates.
So for example, everywhere where we have 200608, it's going to give me August 9th of 2006.
So to get rid of those I use the UNIQUE and then the SORT function, and there's my sorted list, you know.
But the hassle here is we're pointing to O2#.
We're pointing to that helper column and I can't get rid of that helper column.
I've tried.
If I come in here and copy everything, and then come out here to where I have O2# and try and paste that in, I am running into the array of array problem.
And it just it's not going to work.
So I'm curious from a formula point of view, no helper columns, wat would you do to try and extract the minimum value from each month?
Let me know down in the YouTube comments.
Well I want to thank Gagan for sending that great question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They're video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages.
It's a super fast way to learn.
We have a bunch of dates.
We need to figure out the date of the first sale in each month.
This question sent it on YouTube.
Several years and dates in that year.
For example, 2004, 2005, 2006.
There's dates in several months. I need to find the first date in each month, preferably without a helper column, which makes this tougher.
So here I have in E2 to E50, just a bunch of random dates.
I sorted over here just to check.
So, for example like in 7/23, that wasn't the first date of the month.
The first date that we had a sale in in July of 2004 was 7/13.
And so you see here I'm not marking this.
And before I even get into the formulas, I really think after working on this for awhile, a pivot table is the way to go.
So this pivot table: for each month down the left hand side.
Each year across the top.
Shows me the first date that we had that sale.
Let's create the pivot table first.
So the pivot table: we go Insert, PivotTable.
Put it here in an existing worksheet, click OK.
On the left hand side, I'm going to put Dates.
And then in the Values area I'm going to put Dates as well.
And it's going to give me a Count.
But I can double click on the heading there and say that I want to show this as a Min.
I will go in to Number Format and choose Date.
Click OK.
Click OK.
And it's showing me the earliest date for each date, which at this point I realize is silly.
Then we choose the one of the date fields in the Rows area.
Go to Group Field and say we want to roll that up to months and years.
Hey, this is really wild.
Check this out.
This just started happening last week.
This must be a new change.
They now show us that the month is based on the Date field and years is based on the Date field.
I remember a long time ago.
I had a situation where I had two different dates.
I had like a Forecast Date and an Invoice Date and when you rolled those up it was really confusing which is which.
This is kind of a nice nice improvement here that they've renamed that.
I think.
I don't know – it might drive me crazy.
Right now, it seems… it seems good.
Let me know what you think down in the YouTube comments.
Will take the years field and drag it over to columns.
And one more thing, in my pivot table defaults.
I've turned off For Empty Cells.
I always say show 0 instead of the default of blank.
So change that back right.
So now for each month we can see what the first date we had.
I don't know, this seems faster than any formulas.
So that's how I would do it.
But I'm curious here for my formula experts, my Lambda experts.
How you would approach this, from the point of view of a formula.
ou know what I came up with.
Is let's convert that date to the 1st of the month, so eight one 2006 and then do an XLOOKUP.
Looking for the value that's equal to or just greater - that 1 right there is giving us the value that's equal to or just greater.
And then if that's equal to the date.
So in the case of 8/9/2006, if we scroll down here that is the first date in August.
So that will be a match.
We get the word First.
Otherwise quote quote.
And then here under Conditional Formatting we have a rule that's very similar.
Doing the same XLOOKUP seeing if it's equal to the value and highlighting the dates.
Alright, so I'm marking the first date.
And that's all working pretty well without a helper column.
But then I thought, well, let's see if there's some way that we can extract the first date of the month.
So over here I'm looking for the MINIFS.
Look for all of those dates over in column B, and I use the TEXT function here to convert it to YYYYMM.
So check and see if the value here is equal to this current month.
In other words, for all the things that are in August of 2006, let's convert them to 200608, use the MINIFS to find the smallest date in 200608, and that's what we get here.
Now unfortunately, that gives us some duplicates.
So for example, everywhere where we have 200608, it's going to give me August 9th of 2006.
So to get rid of those I use the UNIQUE and then the SORT function, and there's my sorted list, you know.
But the hassle here is we're pointing to O2#.
We're pointing to that helper column and I can't get rid of that helper column.
I've tried.
If I come in here and copy everything, and then come out here to where I have O2# and try and paste that in, I am running into the array of array problem.
And it just it's not going to work.
So I'm curious from a formula point of view, no helper columns, wat would you do to try and extract the minimum value from each month?
Let me know down in the YouTube comments.
Well I want to thank Gagan for sending that great question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They're video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages.
It's a super fast way to learn.
