Formula to yield data with in a date range on rollup

Hi @Emma_Sousa,

If I understand your request correctly, I think you could do this with a conditional rollup. It looks like, right now, you are rolling up linked “Linked Calendar” records, and summing the “PTO Hrs Planned” field, which includes records from every year.

In your “Linked Calendar” table, add a new formula field (perhaps call it “PTO Planned This Year”), and give it a formula something like this:

IF(
   {Year} = YEAR(TODAY()),
   {PTO Hrs Planned}
)

This formula field will reproduce the value in the {PTO Hrs Planned} field, but only if the “Year” of the record is the current year.

Now, back in your “PTO Data” table, rollup THAT field, instead of {PTO Hrs Planned}.

IF(
   {Year} = YEAR(TODAY()),
   {PTO Hrs Planned}
)

Hi

But since my true year is date of hire date - it 3/11/20 to 3/10/21 so how would that change what you suggest?

Regards,

Emma

IF(
   {Year} = YEAR(TODAY()),
   {PTO Hrs Planned}
)

Hi Jeremy

to be more specific…i want to achieve capturing just the time within my date of hire year which is 3/11/2020 to 3/10/2021. I tried to input a formula like the example below, but getting errors

IF({PTO Date}IS_AFTER(‘3/10/2020’),and IS_BEFORE(‘3/11/2021’),{{PTO Hrs Planned})

do you have any helpful tips on how to fix this to achieve what I am trying to do?
Emma

@Emma_Sousa - do you want the rollup to be the static year from 3/11/2020 to 3/10/2021, or do you want it to be a rolling year from 3/11/20** to 3/10/20**?

Hi Jeremy

My apologies - not sure what you mean by static. Let me try explaining this way and perhaps that will answer your question. My date of hire is 3/11…so on 3/11/2020 my vacation time kicked in and goes until 3/10/2021. If my formula can hold my dates including the year 2020 and 2021, then each year on my date of hire anniversary, I can just change the year to meet my needs for the next year.

Emma

Ok - so what you want, ultimately, is a rolling date window, that rolls over to the next “year” period on 3/10 of each year.

The rollover can be made manually, (as you say, by manually changing the year in the formula each year), or it could be done automatically by baking the rollover into the formula – which is preferable, because then you don’t have to think about changing it in a year (which I personally would be unlikely to remember to do).

So here’s a formula that I think should work to do that automatically every year:

IF(
   AND(
      IS_AFTER({PTO Date}, DATETIME_PARSE('3/10/' & YEAR(NOW()))),
      IS_BEFORE({PTO Date}, DATETIME_PARSE('3/11/' & (YEAR(NOW()) + 1)))
   ),
   {PTO Hrs Planned}
)

This uses the IS_AFTER() and IS_BEFORE() checks you were trying to make, and uses DATETIME_PARSE() to turn a date string I provide to the function into a Date that can be used for comparison.

The first DATETIME_PARSE() combines the string '3/10/' with the result of YEAR(NOW()) coerced into a string. So the result will be DATETIME_PARSE('3/10/2020') since YEAR(NOW()) = 2020. On Jan 1, 2021, YEAR(NOW()) will evaluate to 2021.

The second DATETIME_PARSE() does the same thing, except we add 1 to the value produced by YEAR(NOW()). So right now, (YEAR(NOW()) + 1) = 2021. But on Jan 1, 2021, it will change such that it evaluates to 2022.

Try this out and see if it seems to do what you want. Post back if it returns an error.