Formula to yield data with in a date range on rollup

[Emma_Sousa]

Hi All

I’ve been working on a simple time off/vacation tracker. My company has a service they use, but it is really difficult to interpret the data displayed. My company allows our time off to be fully available at your anniversary date of hire. We do not use accrual of time as some companies do.

I’ve almost hit what I’m looking for, but need one more piece to button it up. I basically have two tables.

1 PTO Data holds the critical criteria - PTO types, alloted, planned, balance etc. There is a column which is linked to the 2nd table pulling in the time off dates requested

2. Linked Calendar - is a table, but I display in a calendar view and I simply select a date in the calendar for the time off.

I want the ability to change the time period of how balance is calculated off of the dates requested. Not sure of the best way to do this.

Right now I have two columns…PTO Begins and PTO Ends…this is my range of when my PTO is good for the year. Any ideas what I could do when the new year rolls around that could automatically reset PTO planned to 0?

The only thing that seems to make sense is to somehow see if there is a formula that can be inputted in teh PTO Hrs Planned column where it may be something like…IF dates in Dates Taken (linked table) are between 3/11/2020 and 3/10/2021, then rollup that number.

I’m not sure how to achieve this formula - as the rollup feature only seems to allow for aggregation of some scenario. Can someone offer some help on potential options to achieve this?

The linked table for DATES TAKEN which is displayed in the PTO Data table is below so you can see what I have for columns.

Thanks so much!
Emma

[Jeremy_Oglesby]

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}.

[Emma_Sousa]

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

[Emma_Sousa]

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

[Jeremy_Oglesby]

@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**?

[Emma_Sousa]

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

[Jeremy_Oglesby]

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.