How to select the value of a cell based on the current date and the if another cell has a value of "True"

Question

I am trying to set up a spreadsheet to keep track of my PTO time for work. At the end of each year, we are allowed to carry over 80 hours, so I would like my End of Year Balance to show this, but the problem is that the company uses the closest pay period in the beginning of the next year to use (so I can't just use the final date in the current calendar year).

To try to fix this problem, I added a column to have a checkbox that I can click when I know what date they are going to use for each year. The issue is that my current formula for figuring out the End of Year balance is:

=LOOKUP(MAX($N$5,$N$6),$A:$A,$J:$J)

This is just looking at calendar year (N5 formula is: =DATE(YEAR(TODAY()),1,1) and N6 formula is: =DATE(YEAR(TODAY()),12,31)

What I would like it to do is look at column B (which is where I have my checkbox making each cell either True or False), and then return the balance in column J to a specific cell. I would also need to have it look at the date, so that when I mark the checkbox for next year, it would update the balance for the following year.

So for example, in the following picture, I have the box checked for B87. I would like it to put the value of the corresponding cell in column J (so J87 which currently has a value 80) into cell K2. I would also then like to put the value of the corresponding cell in column M (so M87 which currently has a value of 79.75) into cell K4.

Next year, when we are getting to the end and I want to check my balances for 2026, when I click the checkbox for the next date in column B, I would like the cells K2 and K4 respective to update with the new values that would be in the corresponding columns J and M, respectively.

I am thinking that I want to either use some type of LOOKUP formula, but not sure on how to structure it so that it will also take the date into consideration.

Thank you for any help.

Answer 1

How about this?

Answer 2

Hi There

Please try the following

In cell N7 (or any other cell), enter the formula

=DATE(YEAR(TODAY())+1,1,8)-WEEKDAY(DATE(YEAR(TODAY())+1,1,7))

This will always give you the first Sunday of the following Year, which is the closest pay period for the company, in this case*,* Sunday, 04 January 2026

Then the formulas,

In cell K2 = VLOOKUP($N$7,$A:$M,10,0)

In cell K4 = VLOOKUP($N$7,$A:$M,13,0)

NOTE: Adapt the ranges in the formulas according to your scenario

I hope this helps you

Regards

Jeovany

Answer 3

If my company always used the first Sunday, this would work. The problem is that they don't depending on when the pay period ends, and if they want to extend when people can use their PTO before they transfer anything over the limit to our Long Term Leave. For 2024, the cut-off date was 1/19/25, which was the 3rd Sunday of the month.

Maybe what I am trying to do is a little too complicated for the spreadsheet. I created a very basic spreadsheet to help explain better what I'm trying to do. It might be that I need more than one formula.

Looking at the spreadsheet below, column A has numbers from 1-15, column B has checkboxes, and column C has colors. What I would like to do is have the spreadsheet look at a range (for this spreadsheet, look in column A if the cell has a value from 3 to 10) and see if the checkbox in column B is checked. So basically, if cell in column A >= 3 and <=10, check if cell in column B = TRUE. If it finds a cell in column B that the value is TRUE in the column A cell range, then go to column C and return the value in column C to cell H2. So in the spreadsheet below, it would've looked at cells A6-A13, found a value of TRUE for cell B9, and then put the value in cell C9 ("Green") into cell H2.

Like I said, maybe this is too complicated for the spreadsheet, but thought I'd try.

Answer 4

Yes! This will work great. Thank you! I never knew how to use the "filter" formula before, so I can see this coming in handy. Thank you!!