Solved: Update biweekly date period

[JohnnyBravo]

Happy new year and good tidings to the fine people here at VBAX! Ever since I came here you guys have been nothing but helpful and I am very grateful for all your help.

Although I've been using MS office applications for many years, I'm very new to VBA and know very little. A couple of months ago, I posted a question on how to get started with VBA or how to learn the basics and someone recommended that I buy a "VBA For Dummies" book. So I did.

A user on a computer forum asked how to update a particular cell every other Friday and it really got me curious. Although, I've been trying to come up with a solution, the book I have has next to nothing that covers this particular situation.

Basically the user has a timecard sheet setup in Excel when the employees record the time they came in and time they leave each day. The company is a bi-weekly payroll schedule so the "payperiod ending" date changes every other FRIDAY.

See the screenshot below of the scenario.




I've found the following exmaple on the internet how to update a date on a weekly basis but it doesn't work for a bi-weekly basis.

=TODAY()-MOD(TODAY()-2,7)+7

I figure if I'm going to start learning VBA - I have to start somewhere and this seemed like a challenging but easy enough project.

Basically my idea is to have VBA examine the pay period ending date (Cell C5) and run a If then argument.

So upon opening the worksheet, have a routine that examines cell C5, and compare it to the current date. If current date is more than 13 days past cell C5; replace it with a new date that is 2 weeks from the old one. If it's 13 days or less, then do nothing.

Would this be the most efficient approach? If so, can someone please get me started?

[mdmackillop]

Hi Johnny,
You could run the following when the workbook opens. I've named the cell MyDate for convenience here.
Regards
MD
[VBA]
Private Sub Workbook_Open()
If Now() - Range("MyDate") > 13 Then Range("MyDate") = Int(Now())
End Sub

[/VBA]

[JohnnyBravo]

Hi Johnny,
You could run the following when the workbook opens. I've named the cell MyDate for convenience here.
Regards
MD
[VBA]
Private Sub Workbook_Open()
If Now() - Range("MyDate") > 13 Then Range("MyDate") = Int(Now())
End Sub

[/VBA]

Thanks very much for the very quick response. That is indeed much simpler than what I had in mind! Simple and true - thanks for the quick help mdmackillop.

[VBA]Private Sub Workbook_Open()
Dim CurrentDate As System.DateTime
Dim PEdate As Date
PEdate = Cell (C5)
If CurrentDate > Date + 12 Then
'replace cell C5 with a new date
MsgBox "Please Note: A new payperiod has begun today"
End If

End Sub[/VBA]

[johnske]

You could also use a worksheet activate event similar to what Malcolm has given you, or something along these lines[vba]Option Explicit

Private Sub Worksheet_Activate()
If Range("C5") <= Format(Date, "dd mmm yy") Then Range("C5") = Format(Date + 14, "dd mmm yy")
End Sub[/vba]

[mdmackillop]

Hi Johnny
Happy to help. FYI, your code would be something like the following. In something like this, you need to watch for the actual value of Now(). If it was last set in the afternoon, then it would not reset in ther morning two weeks later. Using Int(Now()) should avoid this potential problem.

[VBA] Private Sub Workbook_Open()
Dim CurrentDate As Date
Dim PEdate As Range

CurrentDate = Int(Now())
Set PEdate = Sheets("Sheet1").Range("C5")
If CurrentDate > PEdate + 12 Then
PEdate = CurrentDate 'replace cell C5 with a new date
MsgBox "Notice: A new payperiod has begun today"
End If

Set PEdate = Nothing

End Sub
[/VBA]

[JohnnyBravo]

Hi Johnny
Using Int(Now()) should avoid this potential problem.

What does the Int do?

Johnske - what advantage does the worksheet event offer that the first method doesn't? I'm still a newb here - learning the ropes so bear with me. Thanks.

[johnske]

What does the Int do?

Johnske - what advantage does the worksheet event offer that the first method doesn't? I'm still a newb here - learning the ropes so bear with me. Thanks.

Int returns an integer value...

No advantage other than the worksheet event code applies to the sheet module that it's written in and you don't have to specify a sheet. If it's written in the workbook module you have to specify what sheet(s) you want the code to apply to... just an alternative