Hi guys,
So I built a PTO request form and I have start/ end date columns.
Now I’m trying to understand how do I calculate what was the PTO balance last month.
Any thoughts before I’m giving up?
Thanks.
1 Like
As an option - add a dashboard view to your board, you can then use chart widgets to show things like PTO last month by person, or total PTO over time etc.
Alternatively have an item per person, and then put PTO in subitems… you could then use the “mirror/rollup” which would sum the subitems and show that on the item.
Hope these may help.
Thanks.
How would rollup/ mirror sum it on the month (and in specific last month) resolution?
Maybe I’m missing something?
You could group by month, or have a view with a filter that only shows last month?
I have a timeline that can overlap across months.
Saying start date 20/Aug and end date of 05/Sep.
I had this same issue and just redid our board a couple weeks ago! So unrelated to your question but I downloaded the [start + end = timeline] integration on our time off/ requests board and have the calendar as a view so when they enter in their start time and end time on the request form it auto-populates the timeline and adds it to the calendar.
populates on my board as you’d imagine:
but gets added to my calendar “VIEW”
To answer your question though on my PTO TOTALS board, what I did was utilized the sub-items.
So I start the parent line with 1 “numbers” column, 1 “mirror” column and 1 “formula” column
Numbers for the total PTO for the year, Mirror for the PTO used, which comes from the sub-items, and the formula is obviously whats left.
You can take this one step farther though and instead of having a Teammate 2022 group you’d have a January February so on → we will go back to that in a minute though.
In the subitems I could write why they were gone or whatever I wanted but I personally do not care so I just use their name.
I first enter the dates THEN I enter in the hours used. I do it in this order because when I “change column” it notifies them what they have left for PTO (the only thing about this though is it doesn’t give them a lump some it sends every single line item like so
When I enter in the sub-items number column it mirrors the sum in the parent line.
The formula column has this formula :
MINUS({Total PTO},{PTO used})
You could have a automation for “every time period” duplicate the group so it’s pulling all the information with it and then just keep adding on so Januarys total remaining would be Jan 1-31 but it would duplicate for February and KEEP all of Jan 1-31 but then ADD Feb 1-28 and then PTO remaining would be cumulative up to that and so on each month.
But I have these two boards separate from one another. There isn’t a way I have found to combine the two because there isn’t any kind of “tracking” system if you are just manually changing numbers in a column adding how many hours they used to the cumulative total like I was before.
lmk if you have questions or anything!
1 Like
Cheers mate,
much appreciated!
What I’m missing is, how do you set those form entries as subitems?
I add them myself on Mondays when I do payroll. But you can add another numbers column to your request board and calculate days between start date and end date and then use that new “general caster” automation to calculate the hours being used and then when “column changes” (the # of hours calculated by the general caster automation) create an item in another board, etc.