May 01, 2018 08:33 AM
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
May 01, 2018 09:05 AM
If the day is 16 or greater, add 1 to the month and then subtract 1 day.
May 01, 2018 11:59 PM
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.
May 22, 2018 10:33 AM
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.
May 22, 2018 11:43 AM
See this reply.