{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Title","Goal","BillAttempt","Wrap Up"],"not_rendered":{"charts":0,"images":7,"macros":0,"external_links":1,"defined_names":19,"comments":0,"pivot_tables":1},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Title","index":0,"n_rows":25,"n_cols":13,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,181]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Image credit: geralt / Pixabay"]

{"sheet":"Goal","index":1,"n_rows":3,"n_cols":1,"rows_kept":3,"cut":false,"hidden":false,"merged":[]}
["Question from Excel is Fun"]
[]
["Can you create a Month to Date Report in a Pivot Table"]

{"sheet":"BillAttempt","index":2,"n_rows":370,"n_cols":11,"rows_kept":370,"cut":false,"hidden":false,"merged":[]}
["Date","Category","Revenue","MTD"]
["2016-01-02","Fruit",1482.48,{"$f":"AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))","v":false},null,null,null,"Sum of Revenue","Date"]
["2016-01-04","Widget",3068.88,{"$f":"AND(MONTH(TODAY())=MONTH(A3),DAY(A3)<=DAY(TODAY()))","v":false},null,null,null,"Category","2016","2017","% Change"]
["2016-01-05","Widget",9490.1299999999992,false,null,null,null,"Fruit",17036.510000000002,13561.759999999998,{"$f":"J4/I4-1","v":-0.20395902681946032}]
["2016-01-06","Widget",4924.96,false,null,null,null,"Gizmo",12888.24,14821.279999999999,{"$f":"J5/I5-1","v":0.14998479233782103}]
["2016-01-07","Gizmo",8733.64,false,null,null,null,"Widget",26466.43,16524.97,-0.37562527322347594]
["2016-01-08","Fruit",4335.71,false,null,null,null,"Grand Total",56391.18,44908.009999999995,-0.20363414987946704]
["2016-01-11","Widget",3984.99,false]
["2016-01-12","Widget",8411.5300000000007,false]
["2016-01-13","Widget",8262.14,false]
["2016-01-14","Fruit",5944.41,false]
["2016-01-15","Fruit",4237.22,false]
["2016-01-18","Fruit",6353.98,false]
["2016-01-19","Gizmo",3255.67,false]
["2016-01-20","Widget",7440.15,false]
["2016-01-21","Fruit",8548.1200000000008,false]
["2016-01-22","Fruit",1519.51,false]
["2016-01-25","Widget",6820.61,false]
["2016-01-26","Widget",3935.78,false]
["2016-01-27","Widget",2877.07,false]
["2016-01-28","Fruit",6938.15,false]
["2016-01-29","Gizmo",5134.84,false]
["2016-02-01","Fruit",6611.05,true]
["2016-02-02","Gizmo",8221.7099999999991,true]
["2016-02-03","Fruit",5555.1,true]
["2016-02-04","Widget",3253.51,false]
["2016-02-05","Widget",6698.25,false]
["2016-02-08","Fruit",5449.14,false]
["2016-02-09","Gizmo",5867.45,false]
["2016-02-10","Gizmo",5073.37,false]
["2016-02-11","Gizmo",6608.88,false]
["2016-02-12","Fruit",9045.7199999999993,false]
["2016-02-15","Fruit",3112.73,false]
["2016-02-16","Widget",6185.22,false]
["2016-02-17","Gizmo",8713.6299999999992,false]
["2016-02-18","Gizmo",8348.5,false]
["2016-02-19","Fruit",6709.64,false]
["2016-02-22","Widget",7474.14,false]
["2016-02-23","Widget",4037.52,false]
["2016-02-24","Fruit",8467.9500000000007,false]
["2016-02-25","Gizmo",6650.34,false]
["2016-02-26","Widget",7678.4,false]
["2016-02-29","Gizmo",5435.8,false]
["2016-03-01","Gizmo",5175.82,false]
["2016-03-02","Widget",9201.18,false]
["2016-03-03","Gizmo",3318.13,false]
["2016-03-04","Widget",4830.16,false]
["2016-03-07","Fruit",2184.46,false]
["2016-03-08","Fruit",5772.06,false]
["2016-03-09","Gizmo",1921.09,false]
["2016-03-10","Fruit",6434.4,false]
["2016-03-11","Widget",4485.3,false]
["2016-03-14","Fruit",2562.48,false]
["2016-03-15","Fruit",8344.17,false]
["2016-03-16","Gizmo",7172.65,false]
["2016-03-17","Gizmo",6925.74,false]
["2016-03-18","Fruit",8410.19,false]
["2016-03-21","Fruit",5093.45,false]
["2016-03-22","Widget",9315.32,false]
["2016-03-23","Gizmo",7168.44,false]
["2016-03-24","Fruit",9507.1200000000008,false]
["2016-03-25","Gizmo",8042.3,false]
["2016-03-28","Gizmo",2661.34,false]
["2016-03-29","Gizmo",4417.66,false]
["2016-03-30","Widget",3847.65,false]
["2016-03-31","Fruit",9880.3700000000008,false]
["2016-04-01","Gizmo",5444.84,{"$f":"AND(MONTH(TODAY())=MONTH(A67),DAY(A67)<=DAY(TODAY()))","v":false}]
["2016-04-04","Fruit",3387.15,false]
["2016-04-05","Widget",8413.58,false]
["2016-04-06","Fruit",8867.7999999999993,false]
["2016-04-07","Fruit",2629.3,false]
["2016-04-08","Fruit",2152.2600000000002,false]
["2016-04-11","Widget",3968.02,false]
["2016-04-12","Gizmo",7443.4,false]
["2016-04-13","Widget",2197.6,false]
["2016-04-14","Widget",2648.29,false]
["2016-04-15","Widget",9238.94,false]
["2016-04-18","Gizmo",9062.08,false]
["2016-04-19","Widget",7564.68,false]
["2016-04-20","Fruit",7127.32,false]
["2016-04-21","Fruit",5752.34,false]
["2016-04-22","Fruit",2212.2600000000002,false]
["2016-04-25","Gizmo",1630.62,false]
["2016-04-26","Fruit",9814.02,false]
["2016-04-27","Fruit",4491.83,false]
["2016-04-28","Fruit",8776.1,false]
["2016-04-29","Fruit",2748.21,false]
["2016-05-02","Fruit",4126.8599999999997,false]
["2016-05-03","Gizmo",6758.75,false]
["2016-05-04","Fruit",7194.6,false]
["2016-05-05","Gizmo",7862.81,false]
["2016-05-06","Fruit",3457.94,false]
["2016-05-09","Gizmo",6381.42,false]
["2016-05-10","Widget",6469.06,false]
["2016-05-11","Widget",7932.64,false]
["2016-05-12","Fruit",3977.35,false]
["2016-05-13","Fruit",4229.12,false]
["2016-05-16","Widget",2749.68,false]
["2016-05-17","Widget",7932.38,false]
["2016-05-18","Widget",6869.81,false]
["2016-05-19","Widget",2259.4299999999998,false]
["2016-05-20","Fruit",5715.71,false]
["2016-05-23","Gizmo",9481.5300000000007,false]
["2016-05-24","Gizmo",1855.62,false]
["2016-05-25","Fruit",8233.73,false]
["2016-05-26","Fruit",1628.1,false]
["2016-05-27","Fruit",1579.15,false]
["2016-05-30","Fruit",3330.83,false]
["2016-05-31","Fruit",8961.99,false]
["2016-06-01","Fruit",4543.93,false]
["2016-06-02","Gizmo",4046.91,false]
["2016-06-03","Widget",2961.02,false]
["2016-06-06","Fruit",8332.5,false]
["2016-06-07","Gizmo",5050.4399999999996,false]
["2016-06-08","Fruit",8545.5,false]
["2016-06-09","Fruit",8488.69,false]
["2016-06-10","Gizmo",4767.57,false]
["2016-06-13","Fruit",6732.24,false]
["2016-06-14","Fruit",5845.88,false]
["2016-06-15","Fruit",9142.84,false]
["2016-06-16","Fruit",5933.78,false]
["2016-06-17","Widget",2893.41,false]
["2016-06-20","Fruit",6550.39,false]
["2016-06-21","Fruit",6296.66,false]
["2016-06-22","Widget",6511.78,false]
["2016-06-23","Gizmo",5569.34,false]
["2016-06-24","Gizmo",8336.98,false]
["2016-06-27","Widget",7228.58,false]
["2016-06-28","Widget",7561.91,false]
["2016-06-29","Fruit",9606.07,false]
["2016-06-30","Widget",9990.41,{"$f":"AND(MONTH(TODAY())=MONTH(A131),DAY(A131)<=DAY(TODAY()))","v":false}]
["2016-07-01","Widget",6872.83,false]
["2016-07-04","Gizmo",7074.08,false]
["2016-07-05","Widget",7842.78,false]
["2016-07-06","Widget",9612.49,false]
["2016-07-07","Widget",9856.17,false]
["2016-07-08","Gizmo",3382.51,false]
["2016-07-11","Widget",4696.6099999999997,false]
["2016-07-12","Fruit",7542.97,false]
["2016-07-13","Fruit",6105.72,false]
["2016-07-14","Gizmo",4941.5600000000004,false]
["2016-07-15","Fruit",5563.62,false]
["2016-07-18","Gizmo",2240.0700000000002,false]
["2016-07-19","Widget",6732.59,false]
["2016-07-20","Gizmo",7215.87,false]
["2016-07-21","Widget",8472.0400000000009,false]
["2016-07-22","Fruit",4784.8900000000003,false]
["2016-07-25","Gizmo",2909.11,false]
["2016-07-26","Gizmo",5171.3900000000003,false]
["2016-07-27","Fruit",5652.65,false]
["2016-07-28","Fruit",8343.4,false]
["2016-07-29","Fruit",4310.84,false]
["2016-08-01","Fruit",7695.94,false]
["2016-08-02","Gizmo",3073.52,false]
["2016-08-03","Fruit",8466.75,false]
["2016-08-04","Gizmo",5281.53,false]
["2016-08-05","Widget",4799.75,false]
["2016-08-08","Gizmo",7615.15,false]
["2016-08-09","Fruit",3130.17,false]
["2016-08-10","Gizmo",6637.54,false]
["2016-08-11","Gizmo",8723.23,false]
["2016-08-12","Fruit",3894.15,false]
["2016-08-15","Widget",1432.43,false]
["2016-08-16","Widget",4065.12,false]
["2016-08-17","Widget",9652.59,false]
["2016-08-18","Fruit",8209.34,false]
["2016-08-19","Gizmo",9499.76,false]
["2016-08-22","Widget",3420.09,false]
["2016-08-23","Widget",5710.74,false]
["2016-08-24","Widget",9990.4500000000007,false]
["2016-08-25","Widget",5034.57,false]
["2016-08-26","Widget",9586.69,false]
["2016-08-29","Widget",8615.4599999999991,false]
["2016-08-30","Gizmo",1352.05,false]
["2016-08-31","Gizmo",5141.96,false]
["2016-09-01","Widget",9353.15,false]
["2016-09-02","Widget",5703.05,false]
["2016-09-05","Gizmo",6566.3,false]
["2016-09-06","Gizmo",5742.79,false]
["2016-09-07","Widget",4688.5200000000004,false]
["2016-09-08","Gizmo",8025.56,false]
["2016-09-09","Fruit",8638.9,false]
["2016-09-12","Widget",2916.64,false]
["2016-09-13","Widget",5269.7,false]
["2016-09-14","Fruit",1560.27,false]
["2016-09-15","Gizmo",1504.15,false]
["2016-09-16","Fruit",8308.17,false]
["2016-09-19","Widget",9245.18,false]
["2016-09-20","Widget",1857.07,false]
["2016-09-21","Widget",9313.9,false]
["2016-09-22","Widget",6855.73,false]
["2016-09-23","Gizmo",2108.6999999999998,false]
["2016-09-26","Fruit",7622.56,false]
["2016-09-27","Widget",1430.12,false]
["2016-09-28","Fruit",2314.31,{"$f":"AND(MONTH(TODAY())=MONTH(A195),DAY(A195)<=DAY(TODAY()))","v":false}]
["2016-09-29","Gizmo",3278.73,false]
["2016-09-30","Gizmo",7182.54,false]
["2016-10-03","Fruit",1513.62,false]
["2016-10-04","Widget",4288.67,false]
["2016-10-05","Gizmo",4552.21,false]
["2016-10-06","Gizmo",5107.91,false]
["2016-10-07","Widget",6680.81,false]
["2016-10-10","Gizmo",4918.9799999999996,false]
["2016-10-11","Widget",2481.39,false]
["2016-10-12","Gizmo",8534.32,false]
["2016-10-13","Widget",1138.68,false]
["2016-10-14","Widget",5345.09,false]
["2016-10-17","Gizmo",8460.17,false]
["2016-10-18","Fruit",1793.43,false]
["2016-10-19","Fruit",6188.48,false]
["2016-10-20","Gizmo",5245.41,false]
["2016-10-21","Gizmo",3536.17,false]
["2016-10-24","Fruit",5709.64,false]
["2016-10-25","Fruit",8055.17,false]
["2016-10-26","Widget",1596.9,false]
["2016-10-27","Widget",8300.51,false]
["2016-10-28","Widget",1823.72,false]
["2016-10-31","Gizmo",3594.7,false]
["2016-11-01","Gizmo",9184.3799999999992,false]
["2016-11-02","Fruit",3841,false]
["2016-11-03","Fruit",6485.52,false]
["2016-11-04","Gizmo",7545.5,false]
["2016-11-07","Fruit",2910.73,false]
["2016-11-08","Fruit",7407.54,false]
["2016-11-09","Fruit",3967.09,false]
["2016-11-10","Gizmo",6029.2,false]
["2016-11-11","Gizmo",1087.73,false]
["2016-11-14","Widget",6115.81,false]
["2016-11-15","Widget",4993.17,false]
["2016-11-16","Fruit",4249.3599999999997,false]
["2016-11-17","Gizmo",9411.24,false]
["2016-11-18","Gizmo",2455.15,false]
["2016-11-21","Fruit",7567.34,false]
["2016-11-22","Widget",7386.88,false]
["2016-11-23","Widget",2515.7199999999998,false]
["2016-11-24","Widget",2564.69,false]
["2016-11-25","Widget",2574.86,false]
["2016-11-28","Gizmo",5585.79,false]
["2016-11-29","Gizmo",9227.1200000000008,false]
["2016-11-30","Fruit",7309.84,false]
["2016-12-01","Gizmo",8439.1299999999992,false]
["2016-12-02","Gizmo",3732.77,false]
["2016-12-05","Gizmo",2930.83,false]
["2016-12-06","Widget",8021.33,false]
["2016-12-07","Gizmo",8626.27,false]
["2016-12-08","Fruit",2210.15,false]
["2016-12-09","Widget",1859.74,false]
["2016-12-12","Gizmo",8566.09,false]
["2016-12-13","Fruit",1350.41,false]
["2016-12-14","Gizmo",4676.7700000000004,false]
["2016-12-15","Fruit",2194.4699999999998,false]
["2016-12-16","Fruit",7663.9,false]
["2016-12-19","Widget",9526.6,false]
["2016-12-20","Fruit",6780.44,false]
["2016-12-21","Fruit",8912.64,false]
["2016-12-22","Widget",2105.7600000000002,false]
["2016-12-23","Widget",4057.72,false]
["2016-12-26","Widget",4794.59,false]
["2016-12-27","Gizmo",7490.66,{"$f":"AND(MONTH(TODAY())=MONTH(A259),DAY(A259)<=DAY(TODAY()))","v":false}]
["2016-12-28","Gizmo",1449.02,false]
["2016-12-29","Gizmo",5248.37,false]
["2016-12-30","Gizmo",4974.58,false]
["2017-01-02","Gizmo",4956.4799999999996,false]
["2017-01-03","Gizmo",5414.67,false]
["2017-01-04","Gizmo",3897.96,false]
["2017-01-05","Fruit",8906.98,false]
["2017-01-06","Fruit",6246.44,false]
["2017-01-09","Gizmo",3587.87,false]
["2017-01-10","Gizmo",9113.2099999999991,false]
["2017-01-11","Fruit",8240.06,false]
["2017-01-12","Fruit",4356.55,false]
["2017-01-13","Gizmo",2857.45,false]
["2017-01-16","Fruit",1166.46,false]
["2017-01-17","Widget",3588.5,false]
["2017-01-18","Gizmo",3294.5,false]
["2017-01-19","Fruit",9326.44,false]
["2017-01-20","Gizmo",2567.88,false]
["2017-01-23","Gizmo",1236.79,false]
["2017-01-24","Widget",8547.2999999999993,false]
["2017-01-25","Fruit",1399.09,false]
["2017-01-26","Gizmo",2370.58,false]
["2017-01-27","Gizmo",2991.56,false]
["2017-01-30","Gizmo",9822.6,false]
["2017-01-31","Gizmo",2495,false]
["2017-02-01","Gizmo",8726.08,true]
["2017-02-02","Gizmo",1989.04,true]
["2017-02-03","Widget",9502.16,true]
["2017-02-06","Widget",8613.08,false]
["2017-02-07","Widget",5551.29,false]
["2017-02-08","Widget",5522.21,false]
["2017-02-09","Widget",5402.37,false]
["2017-02-10","Gizmo",3282.64,false]
["2017-02-13","Gizmo",9741.49,false]
["2017-02-14","Widget",3839.14,false]
["2017-02-15","Widget",7216.7,false]
["2017-02-16","Fruit",4309.8999999999996,false]
["2017-02-17","Gizmo",3678.61,false]
["2017-02-20","Gizmo",6805.1,false]
["2017-02-21","Widget",1621.27,false]
["2017-02-22","Widget",7025.32,false]
["2017-02-23","Widget",1819.84,false]
["2017-02-24","Gizmo",3734.14,false]
["2017-02-27","Widget",7645.65,false]
["2017-02-28","Widget",5695.69,false]
["2017-03-01","Fruit",2721.9,false]
["2017-03-02","Widget",5448.02,false]
["2017-03-03","Gizmo",7056.55,false]
["2017-03-06","Fruit",4175.2299999999996,false]
["2017-03-07","Gizmo",9929.9500000000007,false]
["2017-03-08","Fruit",4548.5200000000004,false]
["2017-03-09","Fruit",1213,false]
["2017-03-10","Fruit",6667.55,false]
["2017-03-13","Widget",9359.49,false]
["2017-03-14","Fruit",6738.75,false]
["2017-03-15","Gizmo",7999.23,false]
["2017-03-16","Widget",4663.6899999999996,false]
["2017-03-17","Fruit",2123.5,false]
["2017-03-20","Fruit",7242.19,false]
["2017-03-21","Fruit",1285.94,false]
["2017-03-22","Fruit",6573.07,false]
["2017-03-23","Gizmo",7493.19,false]
["2017-03-24","Fruit",8960.91,false]
["2017-03-27","Fruit",8173.79,{"$f":"AND(MONTH(TODAY())=MONTH(A323),DAY(A323)<=DAY(TODAY()))","v":false}]
["2017-03-28","Fruit",8196.68,false]
["2017-03-29","Gizmo",3765,false]
["2017-03-30","Fruit",3850.34,false]
["2017-03-31","Widget",3458.05,false]
["2017-04-03","Fruit",7167.98,false]
["2017-04-04","Fruit",4152.2299999999996,false]
["2017-04-05","Widget",4707.0200000000004,false]
["2017-04-06","Widget",4959.13,false]
["2017-04-07","Widget",3577.06,false]
["2017-04-10","Fruit",2241.5500000000002,false]
["2017-04-11","Widget",3281.76,false]
["2017-04-12","Gizmo",3068.27,false]
["2017-04-13","Gizmo",2511.79,false]
["2017-04-14","Gizmo",9241.2199999999993,false]
["2017-04-17","Gizmo",8293.51,false]
["2017-04-18","Gizmo",4773.87,false]
["2017-04-19","Fruit",4705.5200000000004,false]
["2017-04-20","Widget",7540.03,false]
["2017-04-21","Gizmo",3410.7,false]
["2017-04-24","Gizmo",7444.33,false]
["2017-04-25","Widget",4635.34,false]
["2017-04-26","Fruit",4552.47,false]
["2017-04-27","Gizmo",7721.89,false]
["2017-04-28","Fruit",8439.52,false]
["2017-05-01","Fruit",8599.86,false]
["2017-05-02","Widget",8015.82,false]
["2017-05-03","Fruit",6922.58,false]
["2017-05-04","Gizmo",3102.96,false]
["2017-05-05","Fruit",8330.2199999999993,false]
["2017-05-08","Gizmo",9458.7800000000007,false]
["2017-05-09","Fruit",2169,false]
["2017-05-10","Gizmo",2116.5,false]
["2017-05-11","Widget",4616.84,false]
["2017-05-12","Fruit",9054.16,false]
["2017-05-15","Gizmo",3720.34,false]
["2017-05-16","Fruit",7465.78,false]
["2017-05-17","Widget",3550.01,false]
["2017-05-18","Gizmo",3278.78,false]
["2017-05-19","Fruit",8190.68,false]
["2017-05-22","Fruit",9528.61,false]
["2017-05-23","Fruit",1371.3,false]
["2017-05-24","Widget",2508.09,false]
["2017-05-25","Gizmo",6276.6,false]
["2017-05-26","Widget",4550.08,false]
["2017-05-29","Fruit",2260.88,false]
["2017-05-30","Widget",1540.62,false]
["2017-05-31","Widget",8340.81,false]

{"sheet":"Wrap Up","index":3,"n_rows":15,"n_cols":2,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Topics in this Episode"]
[]
[null,"Bill's method"]
[null,"Add a helper cell with a MTD formula"]
[null,"=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))"]
[null,"Add that field as a Slicer where = True"]
[null,"Bonus tip: Group Daily Dates up to Years"]
[null,"Add a calculation outside of the pivot table while avoiding GetPivotData"]
[]
[null,"Mike's approach:"]
[null,"Turn the data into a table using Ctrl+T. This allows more data to be added to the table and the formulas update."]
[null,"SUMIFS with DATE, MONTH, DAY functions"]
[null,"Pressing F4 three times locks a reference to just the column."]
[null,"Watch out - if you drag a Table formula sideways, the columns change. Copy & Paste - no problems"]
[null,"Using TEXT(date,format.  Nice trick with \\1 to insert the number 1 in the text"]