4 Replies Latest reply on Feb 8, 2018 11:53 AM by Lindsay Reed

    Break out individual pay dates from a date range?

    Lindsay Reed

      Hi there!  I have a payment schedule with the date range it's in effect, how often it's paid, and the amount paid.  However, I'd like to calculate how much I'm paying each month and the total of all payments.  Here's what my source data looks like:

       

           

      Expense TypeFrequencyFirst PaymentLast PaymentAmount
      RentMonthly1/1/201612/1/20161000
      RentMonthly1/1/201712/1/20171100
      RentMonthly1/1/201812/1/20181200
      RentMonthly1/1/201912/1/20191300
      UtilitiesQuarterly3/30/201712/30/201990
      ParkingAnnually1/1/20161/1/2018100
      ParkingAnnually1/1/20191/1/2019110
      OtherOne-Time4/11/20174/11/2017100

       

      But here is what I need:

         

      Payment DateExpense TypeAmount
      1/1/2016Rent1000
      1/1/2016Parking100
      2/1/2016Rent1000
      3/1/2016Rent1000
      4/1/2016Rent1000
      6/1/2016Rent1000
      7/1/2016Rent1000
      10/1/2016Rent1000
      11/1/2016Rent1000
      12/1/2016Rent1000
      1/1/2017Rent1100
      2/1/2017Rent1100
      3/1/2017Rent1100
      3/30/2017Utilities90
      4/1/2017Rent1100
      .........
      Total50,000

                               In March 2017 I paid 1200

       

      Any ideas?  Thanks!