Add 2.57 Hours Every Sunday

[Justinian]

My company changed the way they track vacation time on 8/16/20. We used to get vacation hours on our work anniversary (once a year) but now we ae getting 2.57 vacation hours every Sunday (end of pay period).

How do I calculate this so that no matter what day my spreadsheet is opened, Excel looks a today's date an calculates the number of Sundays between today and 8/16/20, ten added 2.57 vacation hours for each Sunday?

 

[test_man2]

Hi test_man2,

Almost! The INT part for the integer needs to be applied to the subtracted dates after they're divided by the number of days between payments, 14.

Let me break it down by using column J of this sheet to contain a date and use that instead of TODAY()

Column K shows the result for each date in J.

Column L shows the result of subtracting 14 from the date differences (this is so that the first date will return a 1 and not a zero).

Column M now shows the result of the division by 14 (number of days between payments) and the INT means it rounds down to the nearest full number so anything less than 14 gives zero, 14 to 27 gives 1, etc.

Column N then multiplies that integer by 2.57 to get the amount of payments so far.

Column O is an alternative so instead of using that "-14" to force the first payment date to a 1 instead of zero you can just add the 1 after the date calculation.

Book1
	J	K	L	M	N	O
1	Date	Formula	Minus	Divide	Hours	Alternative
2	18-Oct-20	0.00	12	0.00	0	0.00
3	19-Oct-20	0.00	13	0.00	0	0.00
4	20-Oct-20	2.57	14	1.00	2.57	2.57
5	21-Oct-20	2.57	15	1.00	2.57	2.57
6	22-Oct-20	2.57	16	1.00	2.57	2.57
7	23-Oct-20	2.57	17	1.00	2.57	2.57
8	24-Oct-20	2.57	18	1.00	2.57	2.57
9	25-Oct-20	2.57	19	1.00	2.57	2.57
10	26-Oct-20	2.57	20	1.00	2.57	2.57
11	27-Oct-20	2.57	21	1.00	2.57	2.57
12	28-Oct-20	2.57	22	1.00	2.57	2.57
13	29-Oct-20	2.57	23	1.00	2.57	2.57
14	30-Oct-20	2.57	24	1.00	2.57	2.57
15	31-Oct-20	2.57	25	1.00	2.57	2.57
16	01-Nov-20	2.57	26	1.00	2.57	2.57
17	02-Nov-20	2.57	27	1.00	2.57	2.57
18	03-Nov-20	5.14	28	2.00	5.14	5.14
19	04-Nov-20	5.14	29	2.00	5.14	5.14
20	05-Nov-20	5.14	30	2.00	5.14	5.14
Sheet1

Cell Formulas
Range	Formula
K2:K20	K2	=INT((J2-DATE(2020,10,20-14))/14)*2.57
L2:L20	L2	=(J2-DATE(2020,10,20-14))
M2:M20	M2	=INT(L2/14)
O2:O20	O2	=(INT((J2-DATE(2020,10,20))/14)+1)*2.57

Thanks for full explanation! I do appreciate it very much!