I am in date hell

[realpoor]

So I have two dates I need to calculate off a 'closing date' which is input by the user. The other two dates are 'Interest Starts' day and the 'First Payment' date. The 'closing date' is stored in a table called [doc_date].


So the first payment date I have already figured out. If the closing date is between the 1st and 10th it will just need to be the next month on the first. If the closing date is between 11th and the 24th it will need to be the next month on the 15th and finally if the closing date is affter the 24th it will need to be in two months on the first. I have the formula for that right here:

IIf(DatePart("d",[doc_date])>25,DateSerial(Year([doc_date]),Month([doc_date])+2,1),IIf(DatePart("d",[doc_date])<10,DateSerial(Year([doc_date]),Month([doc_date])+1,1),DateSerial(Year([doc_date]),Month([doc_date])+1,1)+14))

This seems to be working perfect. So then I started working on the 'interest starts' query field. If the closing date is between the 1st and 10th or 16th and 24th then the date stays the same. If it is between the 11th and 15th the date should just be the current month on the 15th. If the day is greater than the 24th it will be next month on the first. I thought I had this working, but its not working at all.

iif(((DatePart("d",[doc_date])>10)AND(DatePart("d",[doc_date])<15))or(DatePart("d",[doc_date])>25)),[doc_date],iif(DatePart("d",[doc_date])<10),DatePart("m",[doc_date])&"/15/"&DatePart("y",[doc_date]),iif(DatePart(m,DateSerial(Year([doc_date]),Month([doc_date]+1),1))&"/1/"&DatePart(y,DateSerial(Year([doc_date]),Month([doc_date]+1),1)),"Error")))

I have uploaded an xls with the correct dates corresponding to the closing date. https://docs.google.com/file/d/0B067...it?usp=sharing

I appreciate any help you can throw my way.

[rpeare]

IIf(DatePart("d",[doc_date]) Between 1 And 10 Or DatePart("d",[doc_date]) Between 16 And 25,[doc_date],IIf(DatePart("d",[doc_date]) Between 11 And 15,(DateAdd("d",15-(DatePart("d",[doc_date])),[doc_date])),(DateAdd("d",-(DatePart("d",[doc_date])-1),DateAdd("m",1,[doc_date])))))

[ssanfu]

I see rpeare solved your formula problem.

As an alternative (and just for fun), I wrote two functions in VBA.

'Cases
'Close date between 01 and 10 then month+1,1
'Close date between 11 and 15 then month+1,15
'Close date >24 then month+2,1

Function CalcFirstPayment(pCloseDate As Date) As Date
   Dim CloseMonth As Integer
   Dim CloseDay As Integer
   Dim Result As Date

   CloseMonth = Month(pCloseDate)
   CloseDay = Day(pCloseDate)

   Select Case CloseDay
      Case 1 To 10
         Result = DateSerial(Year(pCloseDate), CloseMonth + 1, 1)
      Case 11 To 24
         Result = DateSerial(Year(pCloseDate), CloseMonth + 1, 15)
      Case Is > 24
         Result = DateSerial(Year(pCloseDate), CloseMonth + 2, 1)
   End Select

   CalcFirstPayment = Result

End Function

'Cases
'Close date between (01 and 10) or (16 and 24) then month,1
'Close date between 11 and 15 then month,15
'Close date >24 then month+1,1

Function CalcInterestStart(pCloseDate As Date) As Date
   Dim CloseMonth As Integer
   Dim CloseDay As Integer
   Dim Result As Date

   CloseMonth = Month(pCloseDate)
   CloseDay = Day(pCloseDate)

   Select Case CloseDay
      Case 1 To 10, 16 To 24
         Result = pCloseDate
      Case 11 To 15
         Result = DateSerial(Year(pCloseDate), CloseMonth, 15)
      Case Is > 24
         Result = DateSerial(Year(pCloseDate), CloseMonth + 1, 1)
   End Select

   CalcInterestStart = Result

End Function

In the control source of the text box for the 1st payment date, in the , you would use
=CalcFirstPayment([doc_date])

and in the control source for the interest date
=CalcInterestStart([doc_date])

[realpoor]

Thanks rpeare and ssanfu! Much appreciated! Works liked a charm. I gave you both reputation. If there is another way I can show my gratitude let me know!