Hello Folks,
I am trying to use the below formula, but Monday is not recognizing the EDATE function. Any ideas?
IF(AND(
FORMAT_DATE(DATE(2024,2,DAY({StartDate})),“YYYY-MM-DD”)>={StartDate},
FORMAT_DATE(DATE(2024,2,DAY({StartDate})),“YYYY-MM-DD”)
<FORMAT_DATE(EDATE({StartDate},{DurationInMonths},“YYYY-MM-DD”))
),{DealAmount}/{DurationInMonths},“”)
monday doesn’t have Edate. You’ll probably need to use the ‘push date’ function in the automations or workflow center. You can set an item for a specific date and then ‘push’ it by X dates months
Hey Brad @naethb - if you’re open to using apps, our Smart Timeline + Duration could be helpful for your use case. It works with date, timeline, and duration fields, and lets you trigger automations based on those, making it easier to keep things in sync automatically.
Happy to help if you’d like to explore how it might fit your workflow!
monday’s EDATE() function is undocumented. It adds (or subtracts) months to the given date returning a serial number representing the calculated date. It’s also a bit squirrely with dates and times. I have found that it will work if you add a time to the date you are evaluating. The syntax is EDATE(date, months to add (can be negative)).
Example: FORMAT_DATE(EDATE(“2019-01-20 0:00”, 1)) => “Feb 20, 2019”.
Try this:
IF(
AND(
FORMAT_DATE(DATE(2024, 2, DAY({StartDate})), "YYYY-MM-DD") >= FORMAT_DATE({StartDate}, "YYYY-MM-DD"),
FORMAT_DATE(DATE(2024, 2, DAY({StartDate})), "YYYY-MM-DD") < FORMAT_DATE(EDATE({StartDate} & " 0:00", {DurationInMonths}), "YYYY-MM-DD")
),
{DealAmount} / {DurationInMonths},
""
)
Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday
Thanks Jim. The challenge that I have, is that the formula does not recognize the end date for some reason. See below snapshot. It keeps carrying the forecasted value out past the end date of the project. You will notice for the first project, it has revenue showing in September. Goal is to have the $6k total revenue split in June and July.
Can you put into words what you want the formula to do?
That would help, lol.
I am trying to use Workday to forecast revenue pipeline. The formula is intended to compare the start date and end date, and then blend the contract. Value across those applicable months.
I can use the EDATE function to get the End Date, as shown in an earlier column, but when I try to incorporate into a forecasting column it won’t recognize the end date, and carries the revenue out indefinitely.
If I am getting the details correct, this is what I am seeing:
I suspect that all your Date/Revenue columns have the same formula. You will need to adjust the formula for each column to correspond the date for the column. See below.
Here is the formula for June 2025:
IF(
AND(
FORMAT_DATE(DATE(2025, 6, DAY({StartDate})), "YYYY-MM-DD") >= FORMAT_DATE({StartDate}, "YYYY-MM-DD"),
FORMAT_DATE(DATE(2025, 6, DAY({StartDate})), "YYYY-MM-DD") < FORMAT_DATE(EDATE({StartDate} & " 0:00", {DurationInMonths}), "YYYY-MM-DD")
),
{DealAmount} / {DurationInMonths},
""
)
Here is the formula for August 2025:
IF(
AND(
FORMAT_DATE(DATE(2025, 8, DAY({StartDate})), "YYYY-MM-DD") >= FORMAT_DATE({StartDate}, "YYYY-MM-DD"),
FORMAT_DATE(DATE(2025, 8, DAY({StartDate})), "YYYY-MM-DD") < FORMAT_DATE(EDATE({StartDate} & " 0:00", {DurationInMonths}), "YYYY-MM-DD")
),
{DealAmount} / {DurationInMonths},
""
)
Notice the change in months in the formula.
Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday
Figured it out, this prompt works:
IF(AND(FORMAT_DATE(DATE(2025, 11, DAY({End Date})), “YYYY-MM-DD”) >= FORMAT_DATE({Start Date}, “YYYY-MM-DD”), FORMAT_DATE(DATE(2025, 11, DAY({End Date})), “YYYY-MM-DD”) < FORMAT_DATE({End Date}, “YYYY-MM-DD”)), {Adjusted Revenue} / {Duration (Months)},0)