Getting the next Week Ending's date.

[Talismanic]

Our payroll week ends on Saturday and Payday is Thursday. I am looking for a way to get the date for the next Thursday and Saturday following the current Date().

Any ideas?

 

[Ken Grubb]

If I understand your question correctly,
PeriodEndDate is your Saturday
PayDay is your Thursday.

PeriodEndDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate) + (7 - WeekDay(MyDate)))

PayDay = DateSerial(Year(PeriodEndDate), Month(PeriodEndDate), Day(PeriodEndDate) + 5)

HTH,

Ken Grubb
Burlington, NC, USA

 

[Talismanic]

Ken, I don't understand any of that so I am going to post the code as I have it now and tell you what it is doing.

Dim WeekEndDate As Date
Dim PayDayDate As Date
Dim MyDate As Date

MyDate = Date

WeekEndDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate) + (7 - WeekDay(MyDate)))
PayDayDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate) + 5)

MsgBox ("The Week End Date is " & WeekEndDate & vbCrLf & _
"The Pay Date is " & PayDayDate)

The week ending date is coming up correctly but the Pay Day is not. I am not sure what is going on in the code so I don't know how to modify it to get it to work correctly.

 

[Talismanic]

Still there?

 

[Talismanic]

Never mind, I got it. The math logic I was originaly using was not correct.

Dim WeekEndDate As Date
Dim PayDayDate As Date
Dim MyDate As Date

MyDate = Date

WeekEndDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate) + (7 - WeekDay(MyDate)))
PayDayDate = DateSerial(Year(WeekEndDate), Month(WeekEndDate), Day(WeekEndDate) + 5)

MsgBox ("The Week End Date is " & WeekEndDate & vbCrLf & _
"The Pay Date is " & PayDayDate)

 

[raskew]

Glad you got it, but at least give Ken credit for his solution--it works as advertised. From the debug window:

mydate = date()
PeriodEndDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate) + (7 -WeekDay(MyDate)))
? PeriodEndDate
8/4/01
PayDay = DateSerial(Year(PeriodEndDate), Month(PeriodEndDate), Day(PeriodEndDate)+ 5)
? Payday
8/9/01

 

[Talismanic]

raskew, I give Ken all the credit. I had no idea of how to get to the end result I was looking for.

I still don't realy understand what is going on in the code he gave me. But it worked and that is what is important. Thanks Ken, As I said the problem was with my logic.

The real purpose of the code is to fill three fields in my form when it is opened with the default dates. This is how the code is actualy used in my form if any one is interested.

Private Sub Form_Activate()
Dim WeekEndDate As Date
Dim PayDayDate As Date
Dim MyDate As Date

MyDate = Date

WeekEndDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate) + _
(7 - WeekDay(MyDate)))
PayDayDate = DateSerial(Year(WeekEndDate), Month(WeekEndDate), _
Day(WeekEndDate) + 5)

Me.Week_Ending = WeekEndDate
Me.Pay_Date = PayDayDate
Me.Hire_Date = Date

End Sub

 

[Ken Grubb]

WeekDay(MyDate) returns 1 for Sunday, 2 for Monday, etc.

(7 - WeekDay(MyDate)) returns 6 for Sunday, 5 for Monday, etc. IOW, NumberOfDaysUntilSaturday.

From there, I added NumberOfDaysUntilSaturday to MyDate to get Saturday's date.

Thursday is always 5 days after Saturday, so add 5 days to Saturday's date yielding Thursday's date.

If your Payroll Week Ending date ever changes from Saturday, the formula will get more complex, but still very possible.

HTH,

Ken Grubb
Burlington, NC, USA