Finding Payroll Ending dates that span the new year (1 Viewer)

[hokiewalrus]

I've been developing a payroll problem recently and it started blowing up when it was 2009 but the next payroll ending date was in 2010.

I abandoned what I was using (it had other problems) and recently went to the DateSerial function, but I feel like it will have the same problem:

Public Function GetWeekEndDate(ProvidedDate As Date) As Date

    GetWeekEndDate = DateSerial(Year(ProvidedDate), Month(ProvidedDate), Day(ProvidedDate) + (4 - Weekday(ProvidedDate)))
    
End Function

Since it's basically hard-coding the year to be the same as the ProvidedDate (which is usually just the current date). Is there a way that works when you have to span 2 years?

 

[Steve R.]

From your code it appears that you are trying to compute the coming Wednesday(?) from the ProvidedDate. I would suggest using "DateAdd(interval, number, date)" where the number is "(4 - Weekday(ProvidedDate))". Look-up the "Weekday" function in Access help. Wednesday = 4.

Question, what happens if the provided date is Thursday, which is =5 and would result in a -1. That would give you the prior (past) Wednesday, which may be want you want also.

 

[hokiewalrus]

From your code it appears that you are trying to compute the coming Wednesday(?) from the ProvidedDate. I would suggest using "DateAdd(interval, number, date)" where the number is "(4 - Weekday(ProvidedDate))". Look-up the "Weekday" function in Access help. Wednesday = 4.

Question, what happens if the provided date is Thursday, which is =5 and would result in a -1. That would give you the prior (past) Wednesday, which may be want you want also.

It's funny, since today is Thursday I'm running into the issue you mentioned, it's giving me yesterdays date when what I want is next Wednesday. I have the same issue when I use the DateAdd function you suggested.

But you are correct, I am trying to find the next occurring Wednesday.

 

[Steve R.]

It's funny, since today is Thursday I'm running into the issue you mentioned, it's giving me yesterdays date when what I want is next Wednesday. I have the same issue when I use the DateAdd function you suggested.

But you are correct, I am trying to find the next occurring Wednesday.

Use the "iif" function to test "(4 - Weekday(ProvidedDate))". If the result is negative, it tells you that you will need to add days rather than subtract to compute the next Wednesday.