Need some help with conditional date formula (finding last day of month)!

[Alaena_VanderMo]

Can anyone point me in the right direction here?? Thanks!

If created date is in the last two weeks, and between the 1st and the 15th, payment is due on the 15th.

If created date is in the last two weeks and between the 16th and last day of month, payment is due on the last day of the month.
If payment is due on the 15th, payment will be issued on the 19th
If payment is due on the last day of the month, payment will be issued on the 4th.

So fields are: submitted (created at date), Due Date (formula/date), Issue Date

Submitted: 4/3/2018
Due: 4/15/2018
Issue: 4/19/2018

Submitted: 5/22/2018
Due: 5/31/2018
Issue: 6/4/2018

Submitted: 2/16/2018
Due: 2/28/2018
Issue: 3/4/2018

[Chris_Parker]

If the day is 16 or greater, add 1 to the month and then subtract 1 day.

[Jeremy_Oglesby]

What do you want returned if the created date is greater than the last two weeks?

This will work for your “Due Date” field, assuming a field named “Submitted”:

IF(DATETIME_DIFF(TODAY(),Submitted,'days')<=14,IF(DATETIME_FORMAT(Submitted,'D')<=15,DATEADD(Submitted,15-DATETIME_FORMAT(Submitted,'D'),'day'),IF(DATETIME_FORMAT(Submitted,'D')>15,DATEADD(DATEADD(DATETIME_PARSE(SUBSTITUTE(DATETIME_FORMAT(Submitted,'L'),DATETIME_FORMAT(Submitted,'D'),'1',1),'M/D/YYYY'),1,'month'),-1,'day'),BLANK())))

However, in it’s current state, it will return blank for any “Submitted” dates that are older than 2 weeks (14 days). Something tells me this probably isn’t what you want, but it can be changed if needed.

This will work for your “Issue Date” field, assuming a field named “Due Date”:

IF({Due Date},DATEADD({Due Date},4,'day'),BLANK())

It will return blank if {Due Date} is blank.

[Maria_Dumitriu]

Hi. What about when the due date is not set?

The formula
IF({Completed} = 1, “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Due date}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :running_man: In-progress​:running_man:”))
works great, but I get #ERROR! in cells without deadlines, and I hate that.

Help.

[W_Vann_Hall]

See this reply.