How to calculate a Month-to-Date report using formulas or a pivot table. Bill's method
Add a helper cell with a MTD formula
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)LessThan=DAY(TODAY()))
Add that field as a Slicer where = True
Bonus tip: Group Daily Dates up to Years
Add a calculation outside of the pivot table while avoiding GetPivotData
Mike's approach:
Turn the data into a table using Ctrl+T. This allows more data to be added to the table and the formulas update.
SUMIFS with DATE, MONTH, DAY functions
Pressing F4 three times locks a reference to just the column.
Watch out - if you drag a Table formula sideways, the columns change. Copy & Paste - no problems
Using TEXT(date,format. Nice trick with \1 to insert the number 1 in the text
Add a helper cell with a MTD formula
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)LessThan=DAY(TODAY()))
Add that field as a Slicer where = True
Bonus tip: Group Daily Dates up to Years
Add a calculation outside of the pivot table while avoiding GetPivotData
Mike's approach:
Turn the data into a table using Ctrl+T. This allows more data to be added to the table and the formulas update.
SUMIFS with DATE, MONTH, DAY functions
Pressing F4 three times locks a reference to just the column.
Watch out - if you drag a Table formula sideways, the columns change. Copy & Paste - no problems
Using TEXT(date,format. Nice trick with \1 to insert the number 1 in the text
Transcript of the video:
Bill Jelen: Hey, welcome back.
It's time for another Dueling Excel Podcast.
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from Excel Is Fun.
This is our Episode 181: Month To Date Pivot Table.
Well, hey, today's question- today's idea for this duel is sent in by Mike.
He says, “Can you create a Month To Date report in a pivot table?” Aright, let's go.
So here's what we have, we have two years worth of dates from January 2016 all the way up into 2017.
Now of course I'm recording this in April, it’s April 15th right now when I'm recording my piece of the duel.
And so over here we have a pivot table showing Days on the left-hand side, Category across the top, and Revenue in the heart of the pivot table.
Now, to create a Month To Date report, what I'm going to do is I'm going to say I'm going to add a new helper column over here to my original data and that's going to check for two things.
And because I'm checking for two things I'm going to use the AND function, both things have to be True for it to be Month To Date.
And I'm going to use a function here called TODAY.
TODAY, alright, so I want to know if the MONTH of TODAY()) is = to the MONTH of that date over there in Column A. If that's true, if it's the current month, so in other words if it's April, then check and see if the day of that date over there in A2 is <=DAY of TODAY.
Beautiful thing is when we open this workbook tomorrow or a week from now, the day of today will automatically update and we double click to copy that down.
Alright now, we have to get this extra data into our pivot table so I come here Pivot Table, Analyze and it's not that hard to change the data source, just click that big button there and say that we want to go over to Column D, click OK.
Alright, so now we have that extra field, I'm going to Insert a Slicer based on that Month To Date field and I only want to see how our Month To Date is True.
Now, do we need that Slice to be that big?
No, we can probably make it be two columns and just kind of have it unobtrusive out there on the right-hand side.
So now what we have is all the dates in 2016 and all the dates in 2017; although, it would be really cool to compare these side by side by side.
So I'm going to take that Date field and Analyze.
I'm going to Group the Field, I'm going to group it up to just Years.
I don't actually care about the individual days.
I just want to know Month To Date.
Now, where we are?
So I'll group it up to Years and we'll end up with these 2 years there and I'm going to then rearrange this, put those Years to go across, Categories to go down.
And now I see where we were last year and where we were this year.
Alright now, because I've done grouping I'm no longer allowed to create a calculated field inside the pivot table.
If I wanted to have a year-over-year amount over there, I would right click, Remove Grand Total, alright, and now we are, so, % Change, we're outside of a pivot table pointing inside of the pivot table.
We have to make sure to either turn off GetPivotData or just build a formula like this: =J4/I4-1 and that creates a formula that we can copy down without any hassles at all, like that.
Alright, Mike, let's see what you have.
Mike Girvin: Thanks, MrExcel.
Yes, I sent the question to MrExcel because I did it with formulas and I couldn't figure out how to do it with a standard pivot table and then I remembered seeing over the years, MrExcel do a bunch of cool videos about helper columns and pivot tables.
That is a beautiful formula and a beautiful solution.
So that's how to do it with a pivot table, let's go see how to do it with a formula.
Now, I'm doing this two days after he did it.
F2 I have the TODAY function which is always going to be the date information for today's current date that will be used by the formulas down here because we want it to update.
I've also used an Excel table and it's named FSales.
If I Ctrl+Down Arrow, I see it's 4/14 but I want to be able to add the latest records and have our formulas update included when we jump to the next month.
Ctrl+Up Arrow.
Alright, I have Year Criteria as the column headers, the Category as the row headers, and then the particulars for month and day will come from that cell.
So I'm simply going to use the SUMIFS function since we're adding with multiple conditions, the sum range here's the revenue, we're going to use that great trick for an Excel table.
Right at the top we see that black downward pointing arrow, BAM!
That puts in the proper table name and then in square brackets the field name, comma.
Criteria range, we're going to have to use Date twice, so I'm going to start with Date.
Click, there's the Date Column, comma.
Now I'm in April, so I need to create the condition >= to April 1st.
So comparative operators “>=” in double quotes and I'm going to join it.
Now I have to create some date formula that always looks here and creates the first of the month for this particular year.
So I'm going to use the DATE function.
Year, well I have the Year right as the column header and I'm going to hit the F4 key one, two times to lock the row but not the column so when it moves over here we'll move to 2017, comma, the Month - I'm going to use the MONTH function to get the month number 1 to 12.
That is whatever month is up in that cell, F4 to lock it in all directions, close parentheses and then comma, 1 it’s always going to be the 1st of the month no matter what month this is, close parentheses.
Alright, so that's the criteria.
It will always be >= the first of the month, comma, criteria range two I'm going to get my Date column, comma.
Criteria two, well, this is going to be <= the upper limit, so in “<=” and the &. I'm going to cheat, watch this.
I'm just going to copy this from up here since it's the same thing, Ctrl-C Ctrl-V except for the Day, we have to use the DAY function and always get as our upper limit whatever the day from this particular month is.
F4 to lock it in all directions, close parentheses on Date.
Alright, so that's our criteria two: comma.
Criteria range 3, it is Category.
There it is, comma and there's our row header.
So this one we have to F4 one two three times, lock the column but not the row so when we copy the formula down, we'll move to Gizmo and Widget, close parenthesis and that is the formula.
Drag over, double click and send it down.
I can see there's trouble.
I better come to the last cell diagonally furthest away.
Hit F2.
Now the default behavior for Table Formula Nomenclature is when you copy the formulas to the side, the actual columns move as if they were mixed cell references.
Now we could lock them but I'm not going to do that this time.
Now notice when you copy it down it works fine but when you copy to the side that's when the actual columns move.
So watch this, I'm going to Ctrl+C and Ctrl+V and then that avoids F to the columns from moving when you copy it to the side.
Double click and send it down.
Now our % Change formula = the end amount / the beginning amount -1, Ctrl+Enter, double click and send it down.
Now, before we go test it, now add some new records.
I actually want to create this label up here so it's dynamic.
And the way I'm going to do that is I'm going to say = sign and we're going to do a Text formula so anytime we want text and a formula, you have to put it in: “ and I'm going to type Sales Between, space”& and now I need to extract from that single date there, the first of the month to the end of the month.
I'm going to use the TEXT function.
The TEXT function can take a number dates or serial numbers, comma and use some custom number formatting in ” . I always want to see three-letter abbreviation for the month, mmm, I always want it as the first.
Now if I put a 1 here, comma space yyy, that won't work.
Wants to see that that gives us a value or because it doesn't like that 1.
But we're allowed to insert a single character if we use forward slash, that's in Custom Number formatting.
The mm and the yy will be understood by Custom Number formatting as month and year and now Custom Number format will understand to insert the number 1.
F2 and now we're simply going to: &“ - ”&TEXT of that comma and now we’ll just use straight number formatting: “mmm spaceD, yyy”) Ctrl+Enter.
Now let's just, before we add some data, let's just change this.
Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z.
Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record.
I'm in the last cell of the data set, I hit Tab to add a new record to our data set.
I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference.
If we wanted to check these formula values against the ones MrExcel did: =relative cell reference= click on the sheet, we're going to click right in I4.
We could see our formula up there, Ctrl+Enter.
Actually I'm going to drag it down.
Ctrl+Enter just populated everything I had highlighted.
And of course, FALSE FALSE.
Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.
That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun.
Alright, throw back to MrExcel.
Bill Jelen: Alright, Mike, that's awesome.
So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions.
Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem.
I never knew that one.
And then nice trick there using the heading with the text date format and that \1 to insert a number 1 in the text, \ in any character.
I would allow to insert something, so you might have to do something like \C\O\O\L to get an entire word in there but it would work.
Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match.
Add that field as a Slicer, set the Slicer =True.
And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData.
And I'm interested, I still don't know how Mike did it with his formula.
He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData.
Maybe, maybe he's turned it off.
Alright, well hey, I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel Podcast from MrExcel and Excel Is Fun.
It's time for another Dueling Excel Podcast.
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from Excel Is Fun.
This is our Episode 181: Month To Date Pivot Table.
Well, hey, today's question- today's idea for this duel is sent in by Mike.
He says, “Can you create a Month To Date report in a pivot table?” Aright, let's go.
So here's what we have, we have two years worth of dates from January 2016 all the way up into 2017.
Now of course I'm recording this in April, it’s April 15th right now when I'm recording my piece of the duel.
And so over here we have a pivot table showing Days on the left-hand side, Category across the top, and Revenue in the heart of the pivot table.
Now, to create a Month To Date report, what I'm going to do is I'm going to say I'm going to add a new helper column over here to my original data and that's going to check for two things.
And because I'm checking for two things I'm going to use the AND function, both things have to be True for it to be Month To Date.
And I'm going to use a function here called TODAY.
TODAY, alright, so I want to know if the MONTH of TODAY()) is = to the MONTH of that date over there in Column A. If that's true, if it's the current month, so in other words if it's April, then check and see if the day of that date over there in A2 is <=DAY of TODAY.
Beautiful thing is when we open this workbook tomorrow or a week from now, the day of today will automatically update and we double click to copy that down.
Alright now, we have to get this extra data into our pivot table so I come here Pivot Table, Analyze and it's not that hard to change the data source, just click that big button there and say that we want to go over to Column D, click OK.
Alright, so now we have that extra field, I'm going to Insert a Slicer based on that Month To Date field and I only want to see how our Month To Date is True.
Now, do we need that Slice to be that big?
No, we can probably make it be two columns and just kind of have it unobtrusive out there on the right-hand side.
So now what we have is all the dates in 2016 and all the dates in 2017; although, it would be really cool to compare these side by side by side.
So I'm going to take that Date field and Analyze.
I'm going to Group the Field, I'm going to group it up to just Years.
I don't actually care about the individual days.
I just want to know Month To Date.
Now, where we are?
So I'll group it up to Years and we'll end up with these 2 years there and I'm going to then rearrange this, put those Years to go across, Categories to go down.
And now I see where we were last year and where we were this year.
Alright now, because I've done grouping I'm no longer allowed to create a calculated field inside the pivot table.
If I wanted to have a year-over-year amount over there, I would right click, Remove Grand Total, alright, and now we are, so, % Change, we're outside of a pivot table pointing inside of the pivot table.
We have to make sure to either turn off GetPivotData or just build a formula like this: =J4/I4-1 and that creates a formula that we can copy down without any hassles at all, like that.
Alright, Mike, let's see what you have.
Mike Girvin: Thanks, MrExcel.
Yes, I sent the question to MrExcel because I did it with formulas and I couldn't figure out how to do it with a standard pivot table and then I remembered seeing over the years, MrExcel do a bunch of cool videos about helper columns and pivot tables.
That is a beautiful formula and a beautiful solution.
So that's how to do it with a pivot table, let's go see how to do it with a formula.
Now, I'm doing this two days after he did it.
F2 I have the TODAY function which is always going to be the date information for today's current date that will be used by the formulas down here because we want it to update.
I've also used an Excel table and it's named FSales.
If I Ctrl+Down Arrow, I see it's 4/14 but I want to be able to add the latest records and have our formulas update included when we jump to the next month.
Ctrl+Up Arrow.
Alright, I have Year Criteria as the column headers, the Category as the row headers, and then the particulars for month and day will come from that cell.
So I'm simply going to use the SUMIFS function since we're adding with multiple conditions, the sum range here's the revenue, we're going to use that great trick for an Excel table.
Right at the top we see that black downward pointing arrow, BAM!
That puts in the proper table name and then in square brackets the field name, comma.
Criteria range, we're going to have to use Date twice, so I'm going to start with Date.
Click, there's the Date Column, comma.
Now I'm in April, so I need to create the condition >= to April 1st.
So comparative operators “>=” in double quotes and I'm going to join it.
Now I have to create some date formula that always looks here and creates the first of the month for this particular year.
So I'm going to use the DATE function.
Year, well I have the Year right as the column header and I'm going to hit the F4 key one, two times to lock the row but not the column so when it moves over here we'll move to 2017, comma, the Month - I'm going to use the MONTH function to get the month number 1 to 12.
That is whatever month is up in that cell, F4 to lock it in all directions, close parentheses and then comma, 1 it’s always going to be the 1st of the month no matter what month this is, close parentheses.
Alright, so that's the criteria.
It will always be >= the first of the month, comma, criteria range two I'm going to get my Date column, comma.
Criteria two, well, this is going to be <= the upper limit, so in “<=” and the &. I'm going to cheat, watch this.
I'm just going to copy this from up here since it's the same thing, Ctrl-C Ctrl-V except for the Day, we have to use the DAY function and always get as our upper limit whatever the day from this particular month is.
F4 to lock it in all directions, close parentheses on Date.
Alright, so that's our criteria two: comma.
Criteria range 3, it is Category.
There it is, comma and there's our row header.
So this one we have to F4 one two three times, lock the column but not the row so when we copy the formula down, we'll move to Gizmo and Widget, close parenthesis and that is the formula.
Drag over, double click and send it down.
I can see there's trouble.
I better come to the last cell diagonally furthest away.
Hit F2.
Now the default behavior for Table Formula Nomenclature is when you copy the formulas to the side, the actual columns move as if they were mixed cell references.
Now we could lock them but I'm not going to do that this time.
Now notice when you copy it down it works fine but when you copy to the side that's when the actual columns move.
So watch this, I'm going to Ctrl+C and Ctrl+V and then that avoids F to the columns from moving when you copy it to the side.
Double click and send it down.
Now our % Change formula = the end amount / the beginning amount -1, Ctrl+Enter, double click and send it down.
Now, before we go test it, now add some new records.
I actually want to create this label up here so it's dynamic.
And the way I'm going to do that is I'm going to say = sign and we're going to do a Text formula so anytime we want text and a formula, you have to put it in: “ and I'm going to type Sales Between, space”& and now I need to extract from that single date there, the first of the month to the end of the month.
I'm going to use the TEXT function.
The TEXT function can take a number dates or serial numbers, comma and use some custom number formatting in ” . I always want to see three-letter abbreviation for the month, mmm, I always want it as the first.
Now if I put a 1 here, comma space yyy, that won't work.
Wants to see that that gives us a value or because it doesn't like that 1.
But we're allowed to insert a single character if we use forward slash, that's in Custom Number formatting.
The mm and the yy will be understood by Custom Number formatting as month and year and now Custom Number format will understand to insert the number 1.
F2 and now we're simply going to: &“ - ”&TEXT of that comma and now we’ll just use straight number formatting: “mmm spaceD, yyy”) Ctrl+Enter.
Now let's just, before we add some data, let's just change this.
Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z.
Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record.
I'm in the last cell of the data set, I hit Tab to add a new record to our data set.
I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference.
If we wanted to check these formula values against the ones MrExcel did: =relative cell reference= click on the sheet, we're going to click right in I4.
We could see our formula up there, Ctrl+Enter.
Actually I'm going to drag it down.
Ctrl+Enter just populated everything I had highlighted.
And of course, FALSE FALSE.
Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.
That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun.
Alright, throw back to MrExcel.
Bill Jelen: Alright, Mike, that's awesome.
So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions.
Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem.
I never knew that one.
And then nice trick there using the heading with the text date format and that \1 to insert a number 1 in the text, \ in any character.
I would allow to insert something, so you might have to do something like \C\O\O\L to get an entire word in there but it would work.
Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match.
Add that field as a Slicer, set the Slicer =True.
And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData.
And I'm interested, I still don't know how Mike did it with his formula.
He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData.
Maybe, maybe he's turned it off.
Alright, well hey, I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel Podcast from MrExcel and Excel Is Fun.
