Hello all,
I’m putting a report together for our accounting department that shows current agents draw amounts, balances, start dates, terms and such. So far so good.
I am stuck on coming up with a way to have a custom column that will update a due date field automatically. The dataset given to me does not have a due date currently, only start date and terms in months.
Start Date (date field) + Terms in Months (numeric field) = Due Date
I am thinking this may be the job for DATEADD, but not sure how to make it happen.
Any tips would be much appreciated.
Thanks!
Would it be possible to place an image here Paul. I think this shouldn’t be too difficult. Just want to make sure I’ve got exactly what you need first.
I think ultimately what will need to happen is work out how many days in each month and somehow just add those days onto the start date. May need a supporting table for this with each month and days in month.
Just want to make sure by looking at an image of the table.
It’s a calculated column you need as well isn’t it?
I’m using your Dates M code for the supporting dates table. Starting with Jan 1st, 2018 through Dec 31st, 2018 at the moment.
The idea is to visualize how much time is left before the due date on the terms comes up.
So there’s probably a few ways you can do it I think.
Probably the quickest and easiest way is to create a calculated column that has some logic in it like this
Instead of the 8 reference the term column
This should do it I think
Brilliant! Thank you sir. Worked like a charm.
