Return Last April using VBA

[baz16]

Hi,
I thought this should be relatively easy, but have actually become stuck, I just want a bit of vba code which returns the first day of last april, so in this case with today being 01/02/2016, I would like the date returned to be 01/04/2016.

Thanks in advance

[GarysStudent]

Consider:

Public Function FirstOfLastApril() As Date
   Application.Volatile
   Dim d As Date
   d = Date
   If Month(d) = 4 Then
      FirstOfLastApril = DateSerial(Year(d) - 1, 4, 1)
   Else
      For i = 1 To 9999
         d = d - 1
         If Month(d) = 4 And Day(d) = 1 Then
            FirstOfLastApril = d
            Exit Function
         End If
      Next i
   End If
End Function

We call this "Brute farce"

[SamT]

Function LastAprilFools() As Date
LastAprilFools = CDate(Format(Now, "1-4-yyyy"))
End Function

[Paul_Hossler]

so in this case with today being 01/02/2016, I would like the date returned to be 01/04/2016.

If it were Oct 1, 2016 what would you want? The last April would be April 1, 2016, but the April of last year would be April 1, 2015

[SamT]

Thanks Paul. You made me realize that my reply wasn't right.

Function LastAprilFools() As Date
'If After Apr 1, returns this year's Apr 1 date, else Last year's.

If Date > CDate(Format(Date, "1-4-yyyy")) Then
  LastAprilFools = CDate(Format(Date, "1-4-yyyy"))
Else
    LastAprilFools = CDate(Format(DateAdd("y", -1, Date), "1-4-yyyy"))
End If

End Function

And here is a universal function

Function AnyAprilFirst(SomeDate As Date) As Date
'Returns April 1 of year given in SomeDate
  
AnyAprilFirst = CDate(Format(SomeDate, "1-4-yyyy"))
End Function

[SamT]

Function DateOfQtrOfYear(Qtr As Long, fYear As Date) As Date
'Returns the start date of the specified Qtr of the year given in fDate
'Set FiscalBegins according to the date your fiscal year starts in the form Day-Month-

'Note: Fails if Fiscal year begins before 1 Jan.  UnComment next line
'fYear = DateAdd("y", -1, fYear)

Const FiscalBegins As String = "1-1-"
DateOfQtrOfYear = DateAdd("q", Qtr - 1, Format(fYear, FiscalBegins & "yyyy"))
End Function

[snb]

Sub M_snb()
    MsgBox Format(DateSerial(Year(Date) + (Month(Date) < 4), 4, 1), "dd-mm-yyyy")
End Sub