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?

Excel Facts

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

Well-known Member
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
Yes, the first 2.57 was added on 8/16/20.

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

Perfect!

Thank you!

test_man2

New Member
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

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

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

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

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

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.

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