# Add 2.57 Hours Every Sunday

#### Justinian

##### Well-known Member
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

##### New Member
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
JKLMNO
1DateFormulaMinusDivideHoursAlternative
218-Oct-200.00120.0000.00
319-Oct-200.00130.0000.00
420-Oct-202.57141.002.572.57
521-Oct-202.57151.002.572.57
622-Oct-202.57161.002.572.57
723-Oct-202.57171.002.572.57
824-Oct-202.57181.002.572.57
925-Oct-202.57191.002.572.57
1026-Oct-202.57201.002.572.57
1127-Oct-202.57211.002.572.57
1228-Oct-202.57221.002.572.57
1329-Oct-202.57231.002.572.57
1430-Oct-202.57241.002.572.57
1531-Oct-202.57251.002.572.57
1601-Nov-202.57261.002.572.57
1702-Nov-202.57271.002.572.57
1803-Nov-205.14282.005.145.14
1904-Nov-205.14292.005.145.14
2005-Nov-205.14302.005.145.14
Sheet1
Cell Formulas
RangeFormula
K2:K20K2=INT((J2-DATE(2020,10,20-14))/14)*2.57
L2:L20L2=(J2-DATE(2020,10,20-14))
M2:M20M2=INT(L2/14)
O2:O20O2=(INT((J2-DATE(2020,10,20))/14)+1)*2.57
Thanks for full explanation! I do appreciate it very much!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Replies
4
Views
72
Replies
1
Views
706
Replies
12
Views
523
Replies
6
Views
775
Replies
7
Views
974

1,136,802
Messages
5,677,814
Members
419,722
Latest member
Rizzol

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back