Pull time off data based on the name and date range from another sheet

Jaz693

I have two sheets; one is a PTO calendar where team members enter name, a date, and days of time off. The sheet then calculates the hours of time off. I have another sheet that is broken down by name, project, and weeks (Mondays are the column header, i.e. 1/1, 1/8, etc...).

I want to be able to pull the time off hours from the PTO calendar and populate in the other sheet. The criteria are name and range of dates. I've tried INDEX, and COLLECT, and I can't seem to get it to work. Has anyone had a similar issue? What was the solution?

Paul Newcome

No worries. Lets get started with a few helper columns. We will have one for each potential week that could be overlapped. Assuming a maximum of 56 working hours, this means in theory there could be a maximum of 3 calendar weeks touched by the vacation. Once we get these working, we can move on to your second screenshot and get those calculations going.

First would be [Week 1 Hours] and use this column formula:

=MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row)

Next would be [Week 2 Hours] and use this column formula:

=IFERROR(MIN(((IF(WEEKNUMBER(MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) <> WEEKNUMBER([PTO Date]@row), MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) - IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 7, "")) + 1) * 8, [PTO Hours]@row - MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row)), "")

And finally will be [Week 3 Hours] and use this column formula:

=IFERROR(MIN(((IF(WEEKNUMBER(MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 21)) <> WEEKNUMBER(IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 7, "")), MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 21)) - IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 14, "")) + 1) * 8, [PTO Hours]@row - (MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row) + IFERROR(MIN(((IF(WEEKNUMBER(MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) <> WEEKNUMBER([PTO Date]@row), MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) - IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 7, "")) + 1) * 8, [PTO Hours]@row - MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row)), ""))), "")

Jaz693

I forgot, this is currently what I have: =IF(AND([W4]1 >= {Master PTO Calendar Range 4}, {Master PTO Calendar Range 4} <= [W5]1), {Master PTO Calendar Range 3}, "") This returns hours based on a comparison of the date of PTO to a weekly range but it doesn't look for the name of the person...

I need it to look up by the person's name, compare the date entered to make sure its in the right week, and then return the hours of time off.

Paul Newcome

Are you able to provide some screenshots for context?

Jaz693

here are screenshots.

PTO Capture.jpg is how team members enter their dates for PTO

Where to record time off.JPG is where I wanna capture it

PTO Capture.JPG

where to record time off.JPG

Jaz693

i should state this is how we are trying to build capacity models without purchasing Resource Management ;-)

Paul Newcome

What is the maximum number of days a person can take off in one go, and is there a potential for partial days off?

Jaz693

maximum is 7 days, and no partial time off. Its all 8 or nothing

Paul Newcome

Ok. And with that maximum being 7 days, is that 7 calendar days or 7 work days?

If I put in a Friday date and then use the max of 56 hours, 7 calendar days puts me at the following Friday, but 7 work days puts me at 2 Mondays later.

Example:

5 January 2024 for 56 hours

Calendar days ends on 11 January 2024

Work days ends on 15 January 2024

Jaz693

My apologies I should have been more clear. 7 workdays

Paul Newcome

No worries. Lets get started with a few helper columns. We will have one for each potential week that could be overlapped. Assuming a maximum of 56 working hours, this means in theory there could be a maximum of 3 calendar weeks touched by the vacation. Once we get these working, we can move on to your second screenshot and get those calculations going.

First would be [Week 1 Hours] and use this column formula:

=MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row)

Next would be [Week 2 Hours] and use this column formula:

=IFERROR(MIN(((IF(WEEKNUMBER(MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) <> WEEKNUMBER([PTO Date]@row), MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) - IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 7, "")) + 1) * 8, [PTO Hours]@row - MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row)), "")

And finally will be [Week 3 Hours] and use this column formula:

=IFERROR(MIN(((IF(WEEKNUMBER(MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 21)) <> WEEKNUMBER(IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 7, "")), MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 21)) - IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 14, "")) + 1) * 8, [PTO Hours]@row - (MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row) + IFERROR(MIN(((IF(WEEKNUMBER(MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) <> WEEKNUMBER([PTO Date]@row), MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 14)) - IFERROR([PTO Date]@row + (2 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) < 2, 7, 0) + 7, "")) + 1) * 8, [PTO Hours]@row - MIN(((MIN(WORKDAY([PTO Date]@row, [PTO Hours]@row / 8), [PTO Date]@row + (6 - WEEKDAY([PTO Date]@row)) - IF(WEEKDAY([PTO Date]@row) <= 6, 7, 0) + 7) - [PTO Date]@row) + 1) * 8, [PTO Hours]@row)), ""))), "")

Jaz693

Ok, I've added the calculations to my master PTO sheet, no issues and i see some overlap in the week 1 column, which I assume is a good thing. I did realize that I had the wrong column type, so I changed PTO Date to PTO Start date using date properties to make these formulas work.

Attached is a new screen shot.

helper columns added.JPG

Paul Newcome

That second entry for 7/18 isn't calculating correctly. I thought you said there was a max of 56 hours?

Jaz693

you are right, it is 56. I was playing with numbers and didnt change it back.

Paul Newcome

Ok. So are we working now with the three helper columns in the source sheet?

Jaz693

Yes, I have all three helper columns written and working as expected. i've changed the hours back to 56 for 7/18.

Jaz693

If we wanted to up the PTO time to say 64 hours or more, which part of the helper column 3 would I adjust? I assume I would have to make a helper column4 and add to the formula, i just get lost in where to add to it? And then I assume I would add incrementally, so the next column would adjust for 28 and so on...