Bi-Weekly payroll date (1 Viewer)

[maxim]

I need to calculate payroll due date. Payroll can be weekly, biweekly and monthly. I used DateAdd function and was able to calculate weekly and monthly payroll date but I can't figure out how to calculate biweekly date. Can anyone point me to the right direction?

 

[rainman89]

You said you did dateadd why cant you do

dateadd("ww", 2, [your_date]) or dateadd("d", 14, [yourdate])

 

[maxim]

You said you did dateadd why cant you do

dateadd("ww", 2, [your_date]) or dateadd("d", 14, [yourdate])

I set up a table with first due date and interval (weekly, biweekly and monthly) and I need a due date to be calculated based on todays date. What you suggesting works only for the first date and all following dates get pushed too far since I multiply them by 2 (see the SQL statement)

SELECT Table1.ID, Table1.[payroll date], DateAdd([Interval],[NumberofIntervals],[payroll date]) AS DueDate, DateDiff([Interval],[payroll date],Date())*2 AS NumberOfIntervals
FROM Table1;

It will work if i will store every due date in the table and calculate next due date from the latest date stored in this table. Is this what I need to do?

 

[rainman89]

well now that i think about it, i would think you would need to find the first 2 weeks of the month and the last 2 weeks and if he date falls between the first 2 weeks then this day is payday else its this other day... ill see if i can find some code for this. I feel like i have seen it before

 

[rainman89]

Found this code on google EDIT: here is url
http://bytes.com/topic/access/answers/653209-report-setup-bi-weekly-periods

Dim HardDate As Date
Dim NumberOfDaysSince As Integer
Dim modulus As Integer
Dim DaysToNext As Integer
Dim nextpayday As Date
 
DateToday = Date
HardDate = #5/9/2007#
NumberOfDaysSince = DateDiff("d", HardDate, DateToday)
modulus = NumberOfDaysSince Mod 14
DaysToNext = 14 - modulus
nextpayday = Date + DaysToNext

You might be able to mod it for your own thing. If you store the start date then you can use that for hard date, etc...

 

[Brianwarnock]

Does Biweekly mean that some months have 3 pay days?

I still cant figure the *2

Brian

 

[maxim]

well now that i think about it, i would think you would need to find the first 2 weeks of the month and the last 2 weeks and if he date falls between the first 2 weeks then this day is payday else its this other day... ill see if i can find some code for this. I feel like i have seen it before

Appreciate your help. I searched high and low and couldn't find a solution.

 

[jmiedreich]

Here is a function to return a Bi-Weekly date

Function GetNextBiWeeklyDate(StartDate As Date)


Dim NumWeeks As Double
Dim WeekInMonth As Long
Dim dtmToday As Date


dtmToday = Date


WeekInMonth = (Day((DateDiff("d", 0, dtmToday) - 2) / 7 * 7) - 1) / 7 + 1


NumWeeks = DateDiff("ww", StartDate, dtmToday)


If ((WeekInMonth = 2) Or (WeekInMonth = 4)) Then
     NumWeeks = NumWeeks + 1
End If


GetNextBiWeeklyDate = DateAdd("ww", NumWeeks, StartDate)


End Function

 

[Gasman]

13 year old thread?

 

[theDBguy]

Hi @jmiedreich. Welcome to AWF!

 

[jmiedreich]

13 year old thread?

lol it seemed appropriate.

 

[Pat Hartman]

We love to welcome new members who are so happy to help someone that they reply to a 13 year old thread. We look forward to engaging with you on newer threads