Calculate dates in a date field
P
Pedro Bexiga
create a date field in a task that would calculate x days then send an email/reminder on or before the calculated date.
to give you specifics - this would be used for an HR onboarding where they specify the start date in the task
then another date is calculated for x days later in which we need to be reminded to take another action.
Log In
R
Rod
When a 2nd date is calculated from a 1st date, should be in the 2 ways, later or before also.
Caroline Ginty
Merged in a post:
Formula field not returning correct value with Date + number
H
Heather Shaffer
This formula should work... and it doesn't. Returns a 0. Works fine in Google Sheets. ClickUp seems to get hung-up on the date-to-numeric conversion? Can't "add a day" by adding the Activation Date + a number.
field("Activation date")+MOD(8-WEEKDAY(field("Activation date"),2),7)
Trying to calculate the "next" Monday date, given an existing date (in the "Activation date") field.
Matt McLeod
I have just have a simple HR onboarding use case....Due Date for "First Monthly Review" task is "Commencement Date"(date custom field)+30d. So, I need to use a formula to calculate the task due date. Tried automations but couldn't figure it out.
M
Michael K
also 2, functions should work on field values/variables not just on absolute values , DAYS function for example ....yielding something like
DAYS(field("due date"),today()) and so on
M
Michael K
another very frequent use case: if some date field - today<10 send email/or other action
M
Michael K
this is soooooo missing!!!
P
PRIMETECH
Can I ask a formula for NEXT DUE DATE = DUE DATE + RECUR INTERVAL days
Ralph Sherrin
Is there a way to set a date field based on a calculated field (fx) that returns a date?
Ryan Wychopen
How is this not a thing?
Aviva Wilen
You can use this formula to calculate the days before or after a date field. Replace ETA with your field name and -7 with the number of days you want to displace (positive for future and negative for past)
DATE(YEAR(field("ETA")),SUM(MONTH(field("ETA")),-1),SUM(DAY(EOMONTH(field("ETA"),-1)),DAY(field("ETA")),-7))
L
Lucas Gomez
Aviva Wilen: THANKS!! I Cant seem to replace -7 with number given by other field, how would you do it?
A
Andrew Harrison
Aviva Wilen: Is there a good way to do this, but only counting workdays? Example: I'd love to have a "Draft Due Date" Field that autopopulates for three working days before "Publication Date"
Aviva Wilen
Andrew Harrison: That's even simpler actually - WORKDAY(field("Publication Date"),-3)
R
Russell Acton
Aviva Wilen: I can't seem to get negative number to work? Seems the system will do anything up to 364 days ahead of a date but no more...anytime I include any minus number it won't calculate....any thoughts?
Aviva Wilen
Russell Acton: You only need to change the -7 to be what you want and it should work for positive or negative. Yes, I only made it to to work up to 364 days past. If you wanted to extend it longer, copy the relationship between month and day to extend to year and month
R
Russell Acton
Aviva Wilen: I don’t have the field so used task due date. Then I just changed the -7. Any minus number does work (formula invalid) any positive number calculates fine. Does this only work on custom date fields?
A
Alessa Kubica
Russell Acton: same for me. Minus numbers don't work. I'd like to set up an alert 3 days before the due date. But the formula becomes invalid when i enter -3. WORKDAY(field("Due date"),-3) The formula is invalid
Andrea Speck
Aviva Wilen: THANK YOU!!!! I've been trying to figure out how to calculate out an expiry date based on a purchase date and a shelf life in years. While not the same formula, this really sent me in the right direction.
Load More
→