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?

 

[Toadstool]

Hi Justinian,

8/16/20 was a Sunday but I'll assume that didn't add 2.57 hours and the first 2.57 was added on 8/23/2020 so the formula would be:
=INT((TODAY()-DATE(2020,8,16))/7)*2.57

 

[Justinian]

Yes, the first 2.57 was added on 8/16/20.

 

[Toadstool]

Then
=INT((TODAY()-DATE(2020,8,16-7))/7)*2.57

 

[Justinian]

Perfect!

Thank you!

 

[test_man2]

This may be the very formula that I need as well! How would I adapt it to these parameters?
The first time the amount is applied is on 10/20 and the increment is every 14 days. I think I only need the number where the question mark is.
=INT((TODAY()-DATE(2020,10,20-14))/?)*2.57

 

[MARK858]

If you mean that you want it incrementing every 2nd Tuesday then the ? needs to be a 14

 

[Toadstool]

This may be the very formula that I need as well! How would I adapt it to these parameters?
The first time the amount is applied is on 10/20 and the increment is every 14 days. I think I only need the number where the question mark is.
=INT((TODAY()-DATE(2020,10,20-14))/?)*2.57

Try
=INT((TODAY()-DATE(2020,10,20-14))/14)*2.57

 

[test_man2]

Thank you! That's what I thought!
So let me make sure I understand what this formula is actually doing so that I can adapt it to other situations:
You're converting today's date and the second date to an integer so that you can subtract the two numbers; then you're taking that amount and dividing it by the number of days you had subtracted from the second date; then that final amount is being multiplied by the number of vacation hours per period of time. Is that correct?
One more question (hopefully) - is there a reason why I couldn't just plug in the first date instead of subtracting 14 from it (i.e. 06 instead of 20-14) since it is a constant?

 

[Toadstool]

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