MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Month to Date


April 21, 2017 - by Bill Jelen

Month to Date

How to show Month-To-Date sales in a pivot table. This is a Dueling Excel episode.


Watch Video

  • Bill's method
  • Add a helper cell with a MTD formula
  • =AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=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

Auto-Generated Transcript

  • hey welcome back it's time for another
  • dueling Excel podcast I'm Bill Jelen
  • for MrExcel I'll be joined by Mike or
  • excels one 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 says
  • can you create a month to date report in
  • a pivot table alright 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 is April 15th
  • right now when I'm recording my piece of
  • the duel and so we over here we have a
  • pivot table showing taste 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 gonna do is I'm gonna
  • say I'm gonna add a new helper column
  • over here to my original data and that's
  • gonna check for two things because I'm
  • checking for two things I'm gonna use
  • the and function both things have to be
  • true for it to be month date and I'm
  • gonna use a function here called today
  • today all right so I want to know if the
  • month of today open and closed paren is
  • equal 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 and a two is less than
  • or equal to the 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 all right
  • 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 gonna
  • insert a slicer based on that month to
  • date field and I only want to see where
  • month the date is true now do we need
  • that slice to be that big now 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 gonna take that date field and
  • analyze I'm gonna group the field I'm
  • gonna group it up to just years I don't
  • actually care about the individual days
  • I just want to know month to date where
  • we are so I'll group it up to years and
  • we'll end up with is two years there and
  • I'm gonna 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 all right
  • 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 all right and now we are so
  • percentage change we're outside of a
  • pivot table pointing inside of the pivot
  • table we have to make sure to either
  • turn off get pivot data or just build a
  • formula like this equal J 4 divided by I
  • 4 minus 1 and that creates a formula
  • that we can copy down without any
  • hassles at all like that all right Mike
  • let's see we have 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
  • in 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
  • gonna 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 F sales if I ctrl down arrow I see
  • it's 414 but I want to be able to add
  • the latest records and have our formulas
  • update included when we jump to the next
  • month control
  • uh peril all right 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 this
  • some ifs function since we're adding
  • with multiple conditions the sum range
  • here's the revenue we're gonna use that
  • great trick for an Excel table right at
  • the top we see that black downward
  • pointing arrow balm that puts in the
  • proper table name and then in square
  • brackets the field name comma criteria
  • range we're gonna have to use date twice
  • so I'm gonna start with date click
  • there's the date column comma now I'm in
  • April so I need to create the condition
  • greater than or equal to April 1st so
  • comparative operators greater than or
  • equal to in double quotes and I'm gonna
  • join it now I have to create some date
  • for another that always looks here and
  • creates the first of the month for this
  • particular year so I'm gonna 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 one to
  • twelve that is whatever month is up in
  • that cell f4 to lock it in all
  • directions close parentheses and then
  • come up one it's always going to be the
  • first of the month no matter what month
  • this is cloves parentheses all right so
  • that's the criteria always be greater
  • than or equal to the first of the month
  • comma criteria range two I'm gonna get
  • my date column comma criteria two well
  • this is going to be less than or equal
  • to the upper limit so in double quotes
  • less than or equal to n double quotes
  • and the ampersand I'm gonna cheat watch
  • this I'm just gonna 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
  • from this particular month is f4 to lock
  • it in all directions close parentheses
  • on date right so that's our criteria to
  • 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 ragged 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 gonna control C and
  • control V and then that avoids f2 the
  • columns from moving when you copy it to
  • the side double click and send it down
  • now our percentage change formula equals
  • the end amount divided by the beginning
  • amount minus one control enter double
  • click and send it down now before we go
  • test and 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 gonna say equal sign and
  • we're going to do a text formula so
  • anytime we want text and a format you
  • have to put it in double quotes and I'm
  • gonna type sales between space and
  • double quote ampersand 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 double
  • quotes 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
  • why-why-why that won't work was to see
  • that that gives us a value or because it
  • doesn't like that one 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 for man in
  • his month and year and now custom number
  • format will understand to insert the
  • number one f2 and now we're simply going
  • to ampersand in double quotes space -
  • space and double quote ampersand text o
  • that comma and now we just use straight
  • number for many mmm space D comma space
  • yyy and double quotes parentheses
  • control enter now let's just before we
  • add some data let's just change this
  • pretending that today was showing 3/15
  • slash 2017 just like that all the
  • formulas are updating and our text
  • formula is also control Z now let's go
  • down to the bottom of the data set
  • control down arrow I want to add one new
  • record I'm in the last cell of the data
  • set I had hit tab to add a new record to
  • our data set I'm simply gonna copy this
  • record over here control the control up
  • arrow and there we can see the
  • difference if we wanted to check these
  • formula values against the ones mr.
  • excel did equal sign relative cell
  • reference equal sign click on the sheet
  • we're gonna click right in i-4 we could
  • see our formula up there
  • control enter okay actually I'm gonna
  • drag it down control enter just
  • populated everything I had highlighted
  • and of course false false but guess what
  • equals that amount right there - click
  • control 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
  • we love fun alright throw back to mr.
  • Excel 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 in
  • the formulas will update create that
  • great little formula with some ifs date
  • month and day functions remember
  • pressing F for 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 backslash one to insert a
  • number one in the text backslash in any
  • character I would allow you to insert
  • something so you might have to do
  • something like backslash c backslash o
  • backslash o backslash 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 equal
  • and checking if the month and the day
  • match add that field as a slicer set the
  • slicer equal to true and then bonus tip
  • group daily dates up two years and then
  • added a calculation outside of the pivot
  • table while avoiding get pivot data and
  • I'm interested I still don't know how it
  • Mike did it with his his formula he
  • managed to use the mouse to point to
  • this equal to the something that's not
  • my pivot table it didn't get get pivot
  • data maybe maybe he's turned it off all
  • right 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

Download File

Download the sample file here: Duel181.xlsm

Title Photo: geralt / Pixabay