Date Shunt - Formula to calculate number of days to 15th of the next month

[andy_deg]

Hi Community!

I'm trying to come up with a formula to come to the 15th of the next month.  Unfortunately the formula I've botched together encounters nulls when this passes over a year end. 

The formula I've tried to use is:

DateTimeDiff(DateTimeParse(tostring(DateTimeYear([issued_at])) + "-" +
tostring(DateTimeMonth([issued_at]) +1 ) + "-" +
tostring(15),"%Y-%m-%d"),[issued_at],"days")

Can anyone suggest a more robust formula which will take into account moving across the year end?

Kind Regards

Andy

[LukeM · Accepted solution]

Hi @andy_deg ,

Try this:

DateTimeDiff(
DateTimeAdd(Left([issued_at],7)+'-15',1,'month')
,[issued_at],
'days')

Let me know if it works for you.

Luke