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


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.