up vote 0 down vote favorite

I'm working in vba for excel and need the use of a function that I can input a date that is in the format of the excel converted date (such as 40736). And output the date of the quarter immediately following the input date in the format yyymmdd, where the quarters are Jan 1, Apr 1, Jul 1, and Oct 1. All calculations need to be done in vba, not in the worksheet. As an example: my input might be 40736 (the excel date that corresponds with 7/12/2011. I'd like my output to be 20111001, which corresponds with the following quarter.

I'm really not sure how to even approach this so any help with strategies or proposed peices of code would be great. Thanks

share|improve this question
    
Give it a try and THEN ask when you get stuck... We won't code for you - That's the way things work on this site. – John Bustos Aug 1 '14 at 17:51
up vote 2 down vote accepted

Here is a slight variation of Martin's code to give the first day of the next quarter:

Function GetQuarterDate2(InDate As Date) As Date

    Dim Quarter As Integer
    Dim OutDate As Date
    Dim yr As Long

    Quarter = DatePart("q", InDate)
    yr = Year(InDate)

    If Quarter = 1 Then
        OutDate = DateSerial(yr, 4, 1)
    ElseIf Quarter = 2 Then
        OutDate = DateSerial(yr, 7, 1)
    ElseIf Quarter = 3 Then
        OutDate = DateSerial(yr, 10, 1)
    ElseIf Quarter = 4 Then
        OutDate = DateSerial(yr + 1, 1, 1)
    End If

    GetQuarterDate2 = OutDate

End Function
share|improve this answer
up vote 1 down vote

The below should work, supply your date as the InDate argument;

Function GetQuarterDate(InDate As Date) As Date

  Dim Quarter As Integer
  Dim OutDate As Date

  Quarter = DatePart("q",InDate)

  If Quarter = 1 Then
    OutDate = DateSerial(DatePart("yyyy",InDate),1,1)
  ElseIf Quarter = 2 Then
    OutDate = DateSerial(DatePart("yyyy",InDate),4,1)
  ElseIf Quarter = 3 Then
    OutDate = DateSerial(DatePart("yyyy",InDate),7,1)
  ElseIf Quarter = 4 Then
    OutDate = DateSerial(DatePart("yyyy",InDate),10,1)
  End If

  GetQuarterDate = OutDate

End Function
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.