Skip to main content

Currently I have a field with a Contract Start Date, then I would like a field right next to it that displays the next monthly payment due date, occuring on (Current Month OR Next Month + Day of Month Due).


Given Today is 9/20/19

Eg. {Start Date} = July 3, 2018

{Next Payment Due} = October, 3, 2019.


After October 3, 2019 passes, I want it to say Nov 3, 2019, and so on, for a lot of custom start dates, all keeping the same day of the month that they started.


This would ideally need to work with the calendar block.


Hopefully that makes sense. I’ve been wracking my brain for almost 5 hours on this one dumb problem. I just want to be able to put a start date and have shown just the single next payment due date next to it.

Hi - curious did you ever figure this out?


Emma

I actually did (in some capacity). It functions how I described above, but kicks some errors when due dates fall on 29-30-31, and the next due month doesn’t have that day. I’m sure there are ways to fix this but I didn’t have a ton of time to flesh it out. I utilized a few hidden fields to convert dates and such. Let me see if I can explain it correctly.


Required Fields:




  • Start Date - Date of first payment. This defines the day of the month payment is due


  • Days From DDTM - (Days from Due Date This Month) Finds number of days from the day of the month you’ve set in Start Date. If Start Date is the 15th, and currently it’s the 10th, value will show 5, if it’s the 25th, value will be -10.


  • DDTM (Calc) - (Due Date This Month Calculation) - Takes the Start Date Day, and formats it to be the same day of the current month. If today is the 18th of February, and start date was December 8th, it will show February 8th. We fix this later.


  • DDTM (Date) - (Due Date This Month) - This simply is used to parse an actual Date from the DATETIME formatted calculation field.


  • DDNM (Date) - (Due Date Next Month) - This takes the DDTM Date and adds 1 month. showing all the due dates for March (if you’re in February)


  • Due Date - Finally, this field picks which date to show, within the next 31, by checking if the Days from DDTM is negative or not.


Actual Formulas Used




  • Start Date = Date Field

  • Days From DDTM



DATETIME_DIFF({DDTM (Date)}, TODAY(), ‘days’)




  • DDTM (Calc)



DATETIME_FORMAT(TODAY(), ‘MMMM’)&" “&DATETIME_FORMAT({Start Date}, ‘Do’)&”, "&DATETIME_FORMAT(TODAY(), ‘YYYY’)




  • DDTM (Date)



DATETIME_PARSE({DDTM (Calc)}, ‘MMMM Do, YYYY’)




  • DDNM (Date)



DATEADD({DDTM (Date)}, 1, ‘month’)




  • Due Date



DATETIME_PARSE(IF({Days From DDTM}<0, {DDNM (Date)}, {DDTM (Date)}))



Basically now all you have to do is set the start date, and it will show you the next due date within 31 days. I’ve also colored the table so that when the due date is within 7 days, tomorror or today, it will light up darker orange. Pretty useful.


Hope this helps!

I actually did (in some capacity). It functions how I described above, but kicks some errors when due dates fall on 29-30-31, and the next due month doesn’t have that day. I’m sure there are ways to fix this but I didn’t have a ton of time to flesh it out. I utilized a few hidden fields to convert dates and such. Let me see if I can explain it correctly.


Required Fields:




  • Start Date - Date of first payment. This defines the day of the month payment is due


  • Days From DDTM - (Days from Due Date This Month) Finds number of days from the day of the month you’ve set in Start Date. If Start Date is the 15th, and currently it’s the 10th, value will show 5, if it’s the 25th, value will be -10.


  • DDTM (Calc) - (Due Date This Month Calculation) - Takes the Start Date Day, and formats it to be the same day of the current month. If today is the 18th of February, and start date was December 8th, it will show February 8th. We fix this later.


  • DDTM (Date) - (Due Date This Month) - This simply is used to parse an actual Date from the DATETIME formatted calculation field.


  • DDNM (Date) - (Due Date Next Month) - This takes the DDTM Date and adds 1 month. showing all the due dates for March (if you’re in February)


  • Due Date - Finally, this field picks which date to show, within the next 31, by checking if the Days from DDTM is negative or not.


Actual Formulas Used




  • Start Date = Date Field

  • Days From DDTM



DATETIME_DIFF({DDTM (Date)}, TODAY(), ‘days’)




  • DDTM (Calc)



DATETIME_FORMAT(TODAY(), ‘MMMM’)&" “&DATETIME_FORMAT({Start Date}, ‘Do’)&”, "&DATETIME_FORMAT(TODAY(), ‘YYYY’)




  • DDTM (Date)



DATETIME_PARSE({DDTM (Calc)}, ‘MMMM Do, YYYY’)




  • DDNM (Date)



DATEADD({DDTM (Date)}, 1, ‘month’)




  • Due Date



DATETIME_PARSE(IF({Days From DDTM}<0, {DDNM (Date)}, {DDTM (Date)}))



Basically now all you have to do is set the start date, and it will show you the next due date within 31 days. I’ve also colored the table so that when the due date is within 7 days, tomorror or today, it will light up darker orange. Pretty useful.


Hope this helps!


Hi @Tyler_Hartgrove


This is a great write up of how your database works.


Found it very helpful and I am sure others will too.


Thank you,

Mary K

Reply


Terms & Conditions