Help

Re: Formula for Calculated Dates and Set Dates and no dates

1307 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Robbi_Behr
5 - Automation Enthusiast
5 - Automation Enthusiast

I have set up a series of tasks. Some of the tasks have a regular due date (DUE DATE), some of the tasks have a due date that is calculated (CALCULATED DUE DATE), and some of the tasks need to be done as soon as possible (AS NEEDED).

I would like to make a formula that says
IF the due date is not empty, return the DUE DATE.
but
if the due date is empty, look to see if the CHECKBOX is checked.
IF the checkbox is checked, return the words “ASAP”
but
if the due date is empty AND the checkbox is not checked,
return the CALCULATED DUE DATE.
As an added bonus, I would like the dates in the final output to be written out in the format “Monday, 3/14/22”.
As an added added bonus it would be cool if those dates could be workdays.

I think this is getting complicated because some values are dates and others are words but I can’t quite figure out.

Attached is an image of what I want to do with what info.
image

2 Replies 2
AlliAlosa
10 - Mercury
10 - Mercury

Hi there! Try something like this…

IF({DUE DATE}, DATETIME_FORMAT({DUE DATE}, 'dddd, L'), IF({AS NEEDED}, "ASAP", IF({DATE OF VISIT}, DATETIME_FORMAT(DATEADD({DATE OF VISIT}, {DAY COUNT}, 'days'), 'dddd, L')))) 

I hope this helps!

Robbi_Behr
5 - Automation Enthusiast
5 - Automation Enthusiast

@AlliAlosa thank you so much for your response. That did not work for me. But someone else helped me and we ended up with the following monstrosity, where the columns are named to be equivalent in my above example:

"Hard Due Date (from Tasks) = “Due Date”
“(Date of Visit (DOV) (from Organization)” = “DATE OF VISIT”
“DAY COUNT (from TASKS 2)” = “DAY COUNT”
and
“As received (from TASKS)” = “AS NEEDED”
in the formula below;
We also made any final date that ended on the weekend return the date of the Friday preceding that weekend, and also got the final date returned to have a different DATETIME_FORMAT (the more the pieces I needed got working, the more stuff I wanted the formula to do :grinning_face_with_sweat: .

Anyway, here it is, and - it works! Yay!

IF({Hard Due Date (from Tasks)}, DATETIME_FORMAT({Hard Due Date (from Tasks)},‘dddd M/D/YYYY’),IF({As received (from TASKS)}=1,“As received”,DATETIME_FORMAT(IF(WEEKDAY(DATEADD({Date of Visit (DOV) (from Organization)},0+{DAY COUNT (from TASKS 2)},‘day’))=6,DATEADD({Date of Visit (DOV) (from Organization)},0+{DAY COUNT (from TASKS 2)}-1,‘day’),IF(WEEKDAY(DATEADD({Date of Visit (DOV) (from Organization)},0+{DAY COUNT (from TASKS 2)},‘day’))=0,DATEADD({Date of Visit (DOV) (from Organization)},0+{DAY COUNT (from TASKS 2)}-2,‘day’),DATEADD({Date of Visit (DOV) (from Organization)},0+{DAY COUNT (from TASKS 2)},‘day’))),‘dddd M/D/YYYY’)))