Date formula - Proximity to today between two dates

Platform discussions
, ,
1

Hello -
I have a calendar board I am using to track our team’s PTO and would like to display a grouping by when the PTO is occurring. I initially tried grouping by my timeline field which works fine if the PTO is a single day, but for multiple days, it looks at the end date so an entry for M-F looks like it isn’t happening until Friday. I’d like to create a formula that provides similar values to the out of the box grouping by timeline (today, this week, last week, last month, past, etc.) by querying the proximity to today between the start and end date of the timeline field. Once I have that, I can then group by that formula field.

Is this something someone can help with?

2

This is not a great fix, but what about a formula column that it pulling the weeknum of the timeline? I can only get it to pull the ‘start’ date’s weeknum but it might help?

image
image344×177 6.27 KB

image
image344×177 3.88 KB

1 Like
3

Hi Bill! What if you gave this a try?

You can create a Formula Column and use the following formula:

IF(AND(TODAY() >= {Start Date}, TODAY() <= {End Date}), "Today",
   IF(AND(TODAY() >= {Start Date}, TODAY() <= DATE_ADD(TODAY(), 7, "days")), "This Week",
   IF(AND(TODAY() >= DATE_SUB(TODAY(), 7, "days"), TODAY() < {Start Date}), "Last Week",
   IF(AND(TODAY() >= DATE_SUB(TODAY(), 30, "days"), TODAY() < {Start Date}), "Last Month",
   IF(TODAY() < {Start Date}, "Future", 
   "Past")))))

Breakdown of Formula:

  1. If today is between the start and end date → “Today”
  2. If PTO starts within the next 7 days → “This Week”
  3. If PTO started in the last 7 days but before today → “Last Week”
  4. If PTO started in the last 30 days but before today → “Last Month”
  5. If the PTO start date is in the future → “Future”
  6. If none of the above apply, it must be in the past → “Past”

Here are the steps for implementation:

  1. Add a Formula Column to your board.
  2. Copy & paste the formula above.
  3. Rename the column to something like “PTO Timeframe.”
  4. Group your board by this column.

Now, PTO requests will group logically based on when they actually occur, instead of relying only on the end date.

Lemme know if that works!

T

3 Likes
4

Hi Trevor,
Thanks so much, this looks like what I was looking for. I am getting an unrecognized function when applying this with the sample start and end date columns replaced with my own. Can you look at the modified code below and let me know if there is any modifications I missed?

IF(AND(TODAY() >= {Start Date}, TODAY() <= {End Date}), “Today”,
IF(AND(TODAY() >= {Start Date}, TODAY() <= DATE_ADD(TODAY(), 7, “days”)), “This Week”,
IF(AND(TODAY() >= DATE_SUB(TODAY(), 7, “days”), TODAY() < {Start Date}), “Last Week”,
IF(AND(TODAY() >= DATE_SUB(TODAY(), 30, “days”), TODAY() < {Start Date}), “Last Month”,
IF(TODAY() < {Start Date}, “Future”,
“Past”)))))

Thanks,
Bill

5

Sorry, here is the modified code:

IF(AND(TODAY() >= {Timeline#Start}, TODAY() <= {Timeline#End}), “Today”,
IF(AND(TODAY() >= {Timeline#Start}, TODAY() <= DATE_ADD(TODAY(), 7, “days”)), “This Week”,
IF(AND(TODAY() >= DATE_SUB(TODAY(), 7, “days”), TODAY() < {Timeline#Start}), “Last Week”,
IF(AND(TODAY() >= DATE_SUB(TODAY(), 30, “days”), TODAY() < {Timeline#Start}), “Last Month”,
IF(TODAY() < {Timeline#Start}, “Future”,
“Past”)))))

6

Thanks Desiree. I wasn’t aware of the WEEKNUM. I may use this in a different board.

1 Like
7

Hey Bill!

I think we are getting close! Your formula structure is mostly correct, but the error is likely due to incorrect function usage. T

he functions DATE_ADD and DATE_SUB do not exist in Monday.com’s formula language from what I believe. Instead, could your try to use DAYS to compare dates.

IF(AND(TODAY() >= {Timeline#Start}, TODAY() <= {Timeline#End}), “Today”,
IF(AND(TODAY() >= {Timeline#Start}, {Timeline#Start} <= ADD_DAYS(TODAY(), 7)), “This Week”,
IF(AND(TODAY() >= ADD_DAYS(TODAY(), -7), TODAY() < {Timeline#Start}), “Last Week”,
IF(AND(TODAY() >= ADD_DAYS(TODAY(), -30), TODAY() < {Timeline#Start}), “Last Month”,
IF(TODAY() < {Timeline#Start}, “Future”,
“Past”)))))

We are getting close, I can feel it! hahah!

8

Hi Trevor -
Thanks so much. While this version didn’t work, I was able to get some additional help on another variation using a simplified current, past and future categorization. Here is where it shook out.

IF(AND(DAYS(TODAY(), {Timeline#Start}) > 1, DAYS(TODAY(), {Timeline#End}) > 1), “Past”, IF(OR(OR(AND(DAYS(TODAY(), {Timeline#End}) > 0, DAYS(TODAY(), {Timeline#End}) < 1), AND(DAYS(TODAY(), {Timeline#Start}) > 0, DAYS(TODAY(), {Timeline#Start}) < 1)), AND(DAYS(TODAY(), {Timeline#Start}) > 0, DAYS(TODAY(), {Timeline#End}) < 0)), “Current”,
IF(AND(DAYS(TODAY(), {Timeline#Start}) < 0, DAYS(TODAY(), {Timeline#End}) < 0), “Future”, “”)))

I was then able to group by the new formula and here’s what that looks like:

image
image1125×548 45.2 KB

Thanks again!

1 Like