# Next pay period ending date based on todays date

#### lcarter

##### New Member
I am trying to calculate the next pay period ending based on what ever the current date is. I want to use 08/31/14 from this point forward to calculate every other pay period ending.

If I use the following based on my starting date of "8/31/14" i get 9/14/14 which is correct but I do not know how to reference my start date and so on.

Could some one school me on how I can do this task in Excel please.

Thank You.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
In A1 type 8-31-14
In A2 enter =A1+14
Fill the formula in A2 down and you will have your ending date for the coming pay periods

You can change the date in A1 to whatever and it will recalculate and ending date 14 days from that date.

This formula will always give you the next pay period end date based on today's date

=MOD(DATE(2014,8,31)-TODAY(),14)+TODAY()

if today is on the end date it will give that date

Thank you for the reply.. I am using the following to create a new tab and trying not well to incorporate the next date into the tab name..

Sub Macro3()
Dim shtname As String
ActiveWorkbook.ActiveSheet.
<wbr>Copy After:=Sheets(Sheets.Count)
shtname = InputBox("New Time Sheet Name?", "Sheet name?")
ActiveSheet.Name = shtname
End Sub

Barry Houdini I was using "=round((WEEKNUM(A2)-WEEKNUM(A1))/2,0)" but this only works if a user creates a new tab exactly on the end of the pay period (this usually doesnt happen)..I am still attemping to wrap my head around getting the closest sunday that is the pay end based on "=round((WEEKNUM(A2)-WEEKNUM(A1))/2,0)".

If i use A1 = "8/31/14" A2 = "12/31/14" i get 9 meaning there are 9 more pay periods left in 2014. Yes I think i am missing something!

Thank You

Okay I think i under stand "=MOD(DATE(2014,8,31)-TODAY(),14)+TODAY()" will work. Can you explain how to convert it to VBA?

Sub Macro3()
Dim shtname As String
ActiveWorkbook.ActiveSheet.
<wbr>Copy After:=Sheets(Sheets.Count)
shtname = MOD(DATE(2014,8,31)-TODAY(),14)+TODAY() <---????
ActiveSheet.Name = shtname
End Sub

Thank You Again.

My solution if anyone needs.

Code:
``````Sub Macro3()
Dim dtmStart As Date, dtmEnd As Date
ActiveWorkbook.ActiveSheet.Copy After:=Sheets(Sheets.Count)
dtmStart = "8/31/2014"
dtmEnd = Date
dblDuration = (dtmEnd - dtmStart)
myDate = Abs(dblDuration - 14)

For i = 1 To Worksheets.Count
If Worksheets(i).Name = Format(myDate2, "mmm-dd-yy") Then
exists = True

End If

Next i

MsgBox "Next Pay Period Sheet Already Exists."
ActiveSheet.Delete

If Not exists Then
ActiveSheet.Name = Format(myDate2, "mmm-dd-yy")
End If
End Sub``````

Thanks

Replies
2
Views
280
Replies
3
Views
344
Replies
4
Views
496
Replies
3
Views
496
Legacy 143009
L
Replies
1
Views
198

1,219,518
Messages
6,148,747
Members
450,832
Latest member
Tyroneb90

### 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