Perpetual balance update

[test_man2]

Greetings!
I have a yearly calendar that I keep track of my time off. I have it set up to where when I put a "T" above a date, eight hours is automatically subtracted from my available balance of time off (or "T4 and four hours are subtracted). The issue is my available amount of time off increases every 14 days and the balance that the company shows that I have will not match what my spreadsheet has because I reserve time off throughout the whole year. What formula can I put into a cell that will give me this answer: Beginning balance - balance used + balance earned every 14 days. For example:
Beginning balance: 300 hours (final balance taken from the previous year's calendar and manually entered)
Balance used (5 days in one month, 3 days in another, 1 day in another): 72
Balance earned every 14 days: 15 hours (this can be different based upon the number of years you've been with the company, so I would reference a cell in the formula)

I tried using the formula given in the "Add 2.57 Hours Every Sunday" thread, but it's not working quite right.

I have the rest of the worksheet set up where one cell reads the available number of days (E6), another cell shows the total number of days taken (E7) based upon what has been entered onto the calendar, and a third cell (I6) that shows the number of days available by subtracting the number from E7. Cell E6 reads the number of hours available in cell R68 and divides it by eight. So it looks like the above-requested formula's results needs to show up in R68. My calendar covers cells C8 to Y66 with each month being seven columns x 14 rows (first row is the name of the month, next row is the days of the week, and then every other row after that is the date with the cell above it reserved for the time-off indicator (T or T4)). It is a perpetual calendar in that I just need to put the year in cell U3 and the dates are automagically updated. I wish I could say that I was the creator of this, but I'm not! I just tweaked it for my use.

If you need more information, please feel free to ask!

 

[test_man2]

Again, I really appreciate the time and effort you put into this! You've given me some great formulas to work with! Unfortunately, I do see that some of them won't be able to be used year over year because the beginning of the accrual period won't be 1/1 every year and the end of the accrual period won't be 12/31. It will instead depend on what the number of days are left in the accrual period when the current year ends. So the accrual period next year might start on 1/5 or 1/10 or some other date in January.
Please don't think I'm not grateful! I am very grateful for what you've given me!

 

[test_man2]

It’s been quite a while since I have posted here, but I haven’t given up on finding a solution to this perpetual calendar! And I want to reiterate that I appreciate all that Dossfm0q has posted already! Perhaps this post will help! Hopefully, this post isn’t too long!

Example spreadsheet that this post refers to is in the next post:

First, the request: I’d like a formula that will add all the time I took off during the time period mentioned in columns E and F and place it in the cell in column I that corresponds to that timeframe. This will then show the actual amount of time off I have left in the year.

Second, the flaw in the current spreadsheet: the total number of hours being taken for the year is being subtracted from the available number of hours currently and doesn’t account for any more hours being accrued. For example, over the course of this year, I have allocated 11 days to take off. The current calendar would show that I have taken 88 hours off and had less than 75 hours of time-off left. Instead, it needs to subtract only the number of hours that I have already taken and not future ones as well.

I showed my boss my calendar and he noticed that flaw in it and came up with the idea seen in columns D – J. To make this a calendar able to be used annually, I added the input fields in column B and made formulas in columns E – G in order for them to change based upon those input fields. My boss’s idea was to simply put the number of hours taken off in that particular time period in column I (see examples in cells I13, 15, 20, 25, and 27). What I’d like to do instead is to use a calendar on another worksheet (similar to columns L – Z, including the layout; see Post #4 for the layout) to automatically enter the number of hours taken when a “T” is placed above that day. You can see that demonstrated by looking at the formula in cells I2 and I3. To see that demonstrated, delete the “T” in cell N9 and you’ll see the amount in I3 change. That formula works great as long as the dates are in the same month, but it won’t work from cell I4 and the rest of the column because the timeframe carries over to a new month, which results in columns that haven’t been used before, and rows that have been called before.

Points to remember – not in the example below, but the calendar in Post #4 will change based upon what year is entered; so when 2022 is entered on the calendar, January will start on Saturday and end on Monday, and February will start on Tuesday and end on Monday. So whatever formula is created has to have variables in it to accommodate the ever-changing dates.

-- the variables in cells b1, 2, 4, and 6 will most likely change each year. Next year’s first pay day will probably not be 1/13, and the beginning time-off balance might not be 150. That depends on the ending balance of the previous year. And b6 might changed based upon the whims of the company.

-- (this isn’t related to the requested formula) column J has a conditional formatting on it that if the total goes above 400, the cell turns red.

I’m not at all familiar with array, TRANSPOSE, INDIRECT, and have never worked with MOD, so if any of those functions are used in the solution, please explain them or direct me to a “Excel for Dummies”-level explanation!

 

[test_man2]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Year	2021		Pay #	Pay Begin Date	Pay End Date	Pay Date	Accrued Time Off	Time-off taken	Available TO		JANUARY								FEBRUARY
2	First Pay Day	1/13/2021		1	12/24/2020	1/6/2021	1/13/2021	15	8	157		SUN	MON	TUE	WED	THU	FRI	SAT		SUN	MON	TUE	WED	THU	FRI	SAT
3	Beginning			2	1/7/2021	1/20/2021	1/27/2021	30	16	164							T
4	time-off balance	150		3	1/21/2021	2/3/2021	2/10/2021	45		195							1	2			1	2	3	4	5	6
5	Accrued amt			4	2/4/2021	2/17/2021	2/24/2021	60		210
6	per pay period	15		5	2/18/2021	3/3/2021	3/10/2021	75		225		3	4	5	6	7	8	9		7	8	9	10	11	12	13
7				6	3/4/2021	3/17/2021	3/24/2021	90		240
8				7	3/18/2021	3/31/2021	4/7/2021	105		255		10	11	12	13	14	15	16		14	15	16	17	18	19	20
9				8	4/1/2021	4/14/2021	4/21/2021	120		270				T	T	T	T
10				9	4/15/2021	4/28/2021	5/5/2021	135		285		17	18	19	20	21	22	23		21	22	23	24	25	26	27
11				10	4/29/2021	5/12/2021	5/19/2021	150		300
12				11	5/13/2021	5/26/2021	6/2/2021	165		315		24	25	26	27	28	29	30		28
13				12	5/27/2021	6/9/2021	6/16/2021	180	8	322
14				13	6/10/2021	6/23/2021	6/30/2021	195		345		31
15				14	6/24/2021	7/7/2021	7/14/2021	210	8	352
16				15	7/8/2021	7/21/2021	7/28/2021	225		375
17				16	7/22/2021	8/4/2021	8/11/2021	240		390
18				17	8/5/2021	8/18/2021	8/25/2021	255		405
19				18	8/19/2021	9/1/2021	9/8/2021	270		420
20				19	9/2/2021	9/15/2021	9/22/2021	285	8	427
21				20	9/16/2021	9/29/2021	10/6/2021	300		450
22				21	9/30/2021	10/13/2021	10/20/2021	315		465
23				22	10/14/2021	10/27/2021	11/3/2021	330		480
24				23	10/28/2021	11/10/2021	11/17/2021	345		495
25				24	11/11/2021	11/24/2021	12/1/2021	360	8	502
26				25	11/25/2021	12/8/2021	12/15/2021	375		525
27				26	12/9/2021	12/22/2021	12/29/2021	390	8	532
Sheet1

Cell Formulas
Range	Formula
E2:E27	E2	=F2-13
F2:F27	F2	=G2-7
G2	G2	=B2
H2	H2	=B6
I2	I2	=COUNTIF(L3:R5,"T")*8
G3:G27	G3	=G2+14
H3:H27	H3	=$H$2+H2
I3	I3	=COUNTIF(L5:O9,"T")*8
J2:J27	J2	=$B$4+H2-I2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J27	Cell Value	>400	text	NO