defining a years length(start and end dates)

How about sanitizing the workbook, zip it, and post it here?

here is the program code split in 3 post as too large

the year for 2007 should end on 30/12/07
the year for 2008 should start on 31/12/07
but the tax year ends in 31/12/07
i think the problem is in the taxdb section
or netdb section or dbadjust section in modfunction code.

in macExepPay code
the year for 2007 should end on 30/12/07
the year for 2008 should start on 31/12/07
i think the problem is in the macTaxYear and macCalculateDB sections

modfuction code


Function dbstartdate(rateyear As Integer) As Date

If rateyear = 2006 Then
dbstartdate = "1/1/2006"
ElseIf rateyear = 2007 Then ' Update in 2007
dbstartdate = "1/1/2007"
ElseIf rateyear = 2008 Then ' Update in 2008
dbstartdate = "31/12/2007"
End If
End Function

Function dbenddate(rateyear As Integer) As Date

If rateyear = 2006 Then
dbenddate = "31 / 12 / 2006"
ElseIf rateyear = 2007 Then
dbenddate = "30 / 12 / 2007"
ElseIf rateyear = 2008 Then 'update in 2008
dbenddate = "31 / 12 / 2008"
End If
End Function


Function netdbyear(taxyear As Integer, fromDate As Date, toDate As Date, dbrate As Currency) As Currency
'-----------------------
'Function to Calculate amount of DB
'***created by Seán Lynch 2005
'-------------------------------------- 2004
If taxyear = 2004 Then
'37987 1/1/2004
'38017 31/1/2004
Dim sevenDays As Integer
Dim sevenDays1 As Integer
Dim H As Integer

sevenDays = toDate - fromDate 'Calculates 7 days

H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(xfrom + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
netdb = (H / 6 * dbrate) ' calculates the amount of DB
End If
'-------------------------------------- 2005
If taxyear = 2005 Then
'38353 1/1/2005
'38355 3/2/2005
'38717 31/1/2005
Dim sevenDays As Integer
Dim H As Integer

sevenDays = toDate - fromDate 'Calculates 7 days

H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(xfrom + i) = 1 Then
H = H
Else
H = H + 1
End If
Next

netdb = (H / 6 * dbrate) ' calculates the amount of DB
End If

End Function

Function taxdb(taxyear As Integer, fromDate As Date, toDate As Date, dbrate1 As Currency, dbrate2 As Currency, prevTaxableDays As Integer) As Currency
'-----------------------
'Function to Calculate amount of taxable DB
'***created by Seán Lynch 2005
'-----------------------

Dim sevenDays As Integer
Dim sevenDays1 As Integer
Dim H As Integer
Dim H1 As Integer
'-------------------------------------------------------------
If taxyear = 2002 Then
'---------------------------------------------------------2002
'-------------------------------------------------------------
If fromDate >= 37619 Then 'above 29/12/02
taxdb = taxabledb(fromDate, toDate, dbrate2, prevTaxableDays)
'-------------------------------------------------------------
ElseIf toDate <= 37618 Then '--------Below 28/12/03 0nly
taxdb = taxabledb(fromDate, toDate, dbrate1, prevTaxableDays)
'------------------------------------------------------------
ElseIf (fromDate <= 37618) And (toDate >= 37620) Then '----below 27/12/03 and above 29/12/03
sevenDays = 37619 - fromDate
sevenDays1 = toDate - 37619

taxdb1 = taxabledb(fromDate, 37619, dbrate1, prevTaxableDays) ' how may taxable days from first part of 2002

newTaxableDays1 = taxabledays(fromDate, 37619, dbrate1, prevTaxableDays)
newTaxableDays = prevTaxableDays + newTaxableDays1

taxdb2 = taxabledb(37619, toDate, dbrate2, newTaxableDays)
taxdb = taxdb1 + taxdb2
End If
'-------------------------------------------------------------

ElseIf taxyear = 2006 Then 'need 2006 here
taxdb = taxabledb(fromDate, toDate, dbrate2, prevTaxableDays)
' End If

'---------------------------------------------------------------
ElseIf taxyear = 2007 Then 'need 2007 here
taxdb = taxabledb(fromDate, toDate, dbrate2, prevTaxableDays)
End If

'---------------------------------------------------------------
If taxyear = 2008 Then 'need 2008 here
' '--------------------------------------------------------------
taxdb = taxabledb(fromDate, toDate, dbrate2, prevTaxableDays)


End If
End Function
Function netdbnoyear(fromDate As Date, toDate As Date, dbrate As Currency) As Currency
'-----------------------
'Function to Calculate amount of DB
'***created by Seán Lynch 2005
'-----------------------

Dim sevenDays As Integer
Dim H As Integer

sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next

netdbnoyear = (H / 6 * dbrate) ' calculates the amount of DB

End Function

Function totalDays(fromDate As Date, toDate As Date) As Integer
'-----------------------
'Function to Calculate amount of DB
'***created by Seán Lynch 2005
'-----------------------

Dim sevenDays As Integer
Dim H As Integer

sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next

totalDays = H 'calculates the amount of Days

End Function

Function dbadjust(taxyear As Integer, fromDate As Date, toDate As Date, dbrate1 As Currency, dbrate2 As Currency) As Currency
'-----------------------
'Function to Calculate amount of DB for appropriate tax year.
'***created by Seán Lynch 2005
'-----------------------

Dim sevenDays As Integer
Dim sevenDays1 As Integer
Dim H As Integer
Dim H1 As Integer
'------2002
If taxyear = 2002 Then '1
'---------------------------------------------------------2003
If fromDate >= 37619 Then 'above 29/12/02

adj1 = InputBox("please enter adjustment for 2003 rate")
sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next

dbadjust = (H / 6 * (dbrate2 - adj1)) ' calculates the amount of DB
'-------------------------------------------------------------
ElseIf toDate <= 37618 Then '--------Below 28/12/02 0nly
adj1 = InputBox("please enter adjustment for 2002 rate")
sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next

dbadjust = (H / 6 * (dbrate1 - adj1)) ' calculates the amount of DB
'------------------------------------------------------------
ElseIf (fromDate <= 37618) And (toDate >= 37620) Then '----below 27/12/03 and above 29/12/03
sevenDays = 37619 - fromDate
sevenDays1 = toDate - 37619
adj1 = InputBox("please enter adjustment for 2002 rate")
adj2 = InputBox("please enter adjustment for 2003 rate")

For i = 0 To sevenDays '-----------Loop for less than 27/12/03
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next

For i = 0 To sevenDays1 '-----------Loop for greater than 28/12/03
If Weekday(37619 + i) = 1 Then
H1 = H1
Else
H1 = H1 + 1
End If
Next

dbadjust = (((dbrate1 - adj1) / 6) * H) + (((dbrate2 - adj2) / 6 * H1))
End If
'-End-----2002

'------------------------------------------ ' Update in 2007
ElseIf taxyear = 2007 Then
'---------------------------------------------2007
sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
dbadjust = (H / 6 * dbrate1) ' calculates the amount of DB
End If
'-------------------------------------------- ' Update in 2008
ElseIf taxyear = 2008 Then
''--------------------------------------------2008

sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0

For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
dbadjust = (H / 6 * dbrate1) ' calculates the amount of DB
End If

End Function

Function netdb(taxyear As Integer, fromDate As Date, toDate As Date, dbrate1 As Currency, dbrate2 As Currency) As Currency
'-----------------------
'Function to Calculate amount of DB for appropriate tax year.
'***created by Seán Lynch 2005
'-----------------------

Dim sevenDays As Integer
Dim sevenDays1 As Integer
Dim H As Integer
Dim H1 As Integer

'------2002
If taxyear = 2002 Then '1
'--------------------------------------------------------
If fromDate >= 37619 Then 'above 29/12/02
sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0
For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
netdb = (H / 6 * dbrate2) ' calculates the amount of DB

'-------------------------------------------------------------
ElseIf toDate <= 37618 Then '--------Below 28/12/02 0nly
sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0
For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
netdb = (H / 6 * dbrate1) ' calculates the amount of DB

'------------------------------------------------------------
ElseIf (fromDate <= 37618) And (toDate >= 37620) Then '----below 27/12/03 and above 29/12/03
sevenDays = 37619 - fromDate
sevenDays1 = toDate - 37619
For i = 0 To sevenDays '-----------Loop for less than 27/12/03
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next

For i = 0 To sevenDays1 '-----------Loop for greater than 28/12/03
If Weekday(37619 + i) = 1 Then
H1 = H1
Else
H1 = H1 + 1
End If
Next

netdb = ((dbrate1 / 6) * H) + ((dbrate2 / 6 * H1))
End If
'------2002
'----------------------------------------------------
'ADD in 2006-> onwards here. !!Can use netdbnoyear!!
'----------------------------------------------------
ElseIf taxyear = 2006 Then
'----------------------------------------------------2006
sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0
For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
netdb = (H / 6 * dbrate1) ' calculates the amount of DB
'End If
'------------------------------------------------------
ElseIf taxyear = 2007 Then
'------------------------------------------------------2007
sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0
For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
netdb = (H / 6 * dbrate1) ' calculates the amount of DB
End If
'--------------------------------------------------
If taxyear = 2008 Then
''--------------------------------------------------2008
sevenDays = toDate - fromDate 'Calculates 7 days

H = 0 'set counter to 0
For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
netdb = (H / 6 * dbrate1) ' calculates the amount of DB
End If

End Function

Function taxabledb(fromDate As Date, toDate As Date, dbrate As Currency, prevTaxableDays) As Currency
'-----------------------
'Function to Calculate Taxable amount of DB
'***created by Seán Lynch 2005
'-----------------------

Dim sevenDays As Integer
Dim H As Integer
Dim H2 As Integer 'works out tax days

sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0
For i = 0 To sevenDays
If Weekday(fromDate + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
'----------------------- Working out taxable amount
'---------------------------------'if previous taxable days > 36
If (prevTaxableDays >= 36) Then
taxabledb = (H / 6 * dbrate)
'-----------------------------------------------------------------
'---------------------------------'if previous taxable days = 0
ElseIf (prevTaxableDays = 0) Then
H2 = H - 36
If H2 < 0 Then
H2 = 0
End If

taxabledb = (H2 / 6) * dbrate
'-----------------------------------------------------------------
'---------------------------------'if previous taxable days > 0 and < 36
ElseIf ((prevTaxableDays > 0) And (prevTaxableDays < 36)) Then
H2 = (H + prevTaxableDays) - 36
If H2 < 0 Then
H2 = 0
End If
taxabledb = (H2 / 6) * dbrate
'-----------------------------------------------------------------
End If

End Function

Function taxabledays(fromDate As Date, toDate As Date, dbrate As Currency, prevTaxableDays) As Currency
'-----------------------
'Function to Calculate Taxable Days
'***created by Seán Lynch 2005
'-----------------------

Dim sevenDays As Integer
Dim H As Integer

sevenDays = toDate - fromDate 'Calculates 7 days
H = 0 'set counter to 0
For i = 0 To sevenDays
If Weekday(xfrom + i) = 1 Then
H = H
Else
H = H + 1
End If
Next
taxabledays = H

End Function

macExepPay code

Sub macCalculateDB()
Dim taxyear As Integer

taxyear = Range("a1")

'Update in 2008
'If taxyear = 2008 Then 'Update in 2008
' dbrate1 = 2007 '* If the 2008 tax year uses part of the 2007 rate else 2008
' dbrate2 = 2008

If taxyear = 2008 Then ' update in 2008
dbrate1 = 2008 '* if the 2008 tax year doesnt use part of the 2007 rates
dbrate2 = 2008

ElseIf taxyear = 2007 Then 'Update in 2008 -> ElseIf
dbrate1 = 2007
dbrate2 = 2007

'=
Sub macTaxYear()
'----------------------------------------------------------
'Macro that returns the years between from and to dates
'Created by Seán Lynch 2005
'----------------------------------------------------------

Dim xfrom As Date
Dim yto As Date
Dim taxyearfrom As Integer
Dim taxyearto As Integer
Dim yearcounter As Integer
Dim r As Integer

Sheets("calcs").Range("A1:e19").ClearContents

xfrom = Range("A20")
yto = Range("B20")

taxyearfrom = Year(xfrom)
taxyearto = Year(yto)
yearcounter = (taxyearto - taxyearfrom) + 1

r = 1
H = 2

While yearcounter > 0
Sheets("calcs").Cells(r, 3) = taxyearfrom
Sheets("calcs").Cells(r, 4) = dbstartdate(taxyearfrom)
Sheets("calcs").Cells(r, 5) = dbenddate(taxyearfrom)

'If taxyearfrom = 2008 Then 'Update in 2008
' Sheets("calcs").Cells(r, 1) = 2007 '* If the 2008 tax year uses part of the 2007 rate else 2008
' Sheets("calcs").Cells(r, 2) = 2008

If taxyearfrom = 2008 Then
Sheets("calcs").Cells(r, 1) = 2008 'update in aug 2008
Sheets("calcs").Cells(r, 2) = 2008 '* if the 2008 tax year doesn't use part of the 2007 rate

ElseIf taxyearfrom = 2007 Then 'Update in 2008 to ElseIf
Sheets("calcs").Cells(r, 1) = 2007
Sheets("calcs").Cells(r, 2) = 2007



Else: MsgBox ("Sorry there is no such rate please enter rates " & taxyearfrom)

End If

r = r + 1
H = H + 1

yearcounter = yearcounter - 1
taxyearfrom = taxyearfrom + 1

Wend

Sheets("calcs").Cells(1, 4) = xfrom
Sheets("Calcs").Cells(r - 1, 5) = yto

End Sub

thanks
been at this all last week to fix but can't
didnt write this code was just asked to update it for 2008

ronan.

sorry dont know how to do that.i have zipped the whole program but can't paste it onto the forum for help

here is the zipped program

problem1---- when date 24/12/07-6/1/08 the calculation(personal-full calculate) comes out correct i.e.
6 days at rate 185.80 and 6 days at 197.30. showing the end of 2007 is 20/12/07 and the start of 2008 is 31/12/07.
but if you enter just 24/12/07- 31/12/07 the result show a calulation of 7 days at 185.80 instead of 6 days at 185.0 and 1 day at 197.80.

problem 2 ----- tax is calulated if the number of days is over 36. but since the tax year ends on the 31/12/07 (unlike the 30/12/07 whe the new pay rates start) the way i have the calculator now will calculate the end of the tax year as 31/12/07 if the date ends on the 31/12/07 but wont calculate 31/12/07 as the last date of 2007 if 2008 dates are included too.

cheers for any help

to zip the program i could only get it down to 121mb which is over the forum limit so had to copy the code into .txt file called mod function and macExceptional hope that this doesnt ruin the program

ronan