Add 2.57 Hours Every Sunday

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,480
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 28, 2007
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top