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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,822
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Aug 9, 2009
Messages
1,480
Office Version
  1. 365
Platform
  1. Windows
Yes, the first 2.57 was added on 8/16/20.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,822
Office Version
  1. 2016
Platform
  1. Windows
Then
=INT((TODAY()-DATE(2020,8,16-7))/7)*2.57
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,480
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Perfect!

Thank you!
 

test_man2

New Member
Joined
Aug 28, 2007
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,822
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 28, 2007
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,822
Office Version
  1. 2016
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
 

Forum statistics

Threads
1,136,796
Messages
5,677,785
Members
419,720
Latest member
kurman

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