Next pay period ending date based on todays date

[lcarter]

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.

 

[Latchmaker]

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.

 

[barry houdini]

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

 

[lcarter]

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

 

[lcarter]

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.
​

 

[lcarter]

My solution if anyone needs.

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)
    myDate2 = DateAdd("d", myDate, Date)
        
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = Format(myDate2, "mmm-dd-yy") Then
        exists = True
        
    End If
    
Next i

Application.DisplayAlerts = False
MsgBox "Next Pay Period Sheet Already Exists."
ActiveSheet.Delete
Application.DisplayAlerts = True

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

Thanks