Hello,
If anyone could help me with this UDF, I will really appreciate it.
At work, I use a table that counts the # of days between a Contract Start Date and the Contract End Date.
In the UDF I've been able to write so far (See below) , I have no issues with the first year as long as the function is counting days between the same calendar year. The problems start when I need to break the # of days among multimple years (See link below to sample table).
For example, if an contract goes from 01-Jul-06 to 30-Jun-07, how do I make the function count only the days belong to calendar year 2007 under the 2007 Column?
Link to sample table image:
http://docs.google.com/Present?docID=ddr9b8g_2hp5k7pfg&revision=_latest&start=0&theme=blank&cwj=true
UDF (under construction)
----------------------------------------------------------------------------
Function ContractDays2006(OrderStartDate As Date, OrderEndDate As Date)
Dim StartDate As Date
Dim EndDate As Date
'38718 = 01-Jan-06
'39082 = 31-Dec-06
'39083 = 01-Jan-07
'39447 = 31-Dec-07
If OrderStartDate > 39082 Then
ContractDays2006 = 0
Exit Function
Else
StartDate = OrderStartDate
End If
If OrderEndDate > 39082 Then
EndDate = 39083
Else
EndDate = OrderEndDate
End If
ContractDays2006 = EndDate - StartDate
End Function
-----------------------------------------------------------------------
Thank you.
If anyone could help me with this UDF, I will really appreciate it.
At work, I use a table that counts the # of days between a Contract Start Date and the Contract End Date.
In the UDF I've been able to write so far (See below) , I have no issues with the first year as long as the function is counting days between the same calendar year. The problems start when I need to break the # of days among multimple years (See link below to sample table).
For example, if an contract goes from 01-Jul-06 to 30-Jun-07, how do I make the function count only the days belong to calendar year 2007 under the 2007 Column?
Link to sample table image:
http://docs.google.com/Present?docID=ddr9b8g_2hp5k7pfg&revision=_latest&start=0&theme=blank&cwj=true
UDF (under construction)
----------------------------------------------------------------------------
Function ContractDays2006(OrderStartDate As Date, OrderEndDate As Date)
Dim StartDate As Date
Dim EndDate As Date
'38718 = 01-Jan-06
'39082 = 31-Dec-06
'39083 = 01-Jan-07
'39447 = 31-Dec-07
If OrderStartDate > 39082 Then
ContractDays2006 = 0
Exit Function
Else
StartDate = OrderStartDate
End If
If OrderEndDate > 39082 Then
EndDate = 39083
Else
EndDate = OrderEndDate
End If
ContractDays2006 = EndDate - StartDate
End Function
-----------------------------------------------------------------------
Thank you.
