PDA

View Full Version : Return Last April using VBA



baz16
02-01-2016, 07:31 AM
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
02-02-2016, 01:15 PM
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
02-02-2016, 02:19 PM
Function LastAprilFools() As Date
LastAprilFools = CDate(Format(Now, "1-4-yyyy"))
End Function

Paul_Hossler
02-02-2016, 09:25 PM
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
02-02-2016, 10:16 PM
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
02-02-2016, 10:53 PM
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
02-03-2016, 01:34 AM
Sub M_snb()
MsgBox Format(DateSerial(Year(Date) + (Month(Date) < 4), 4, 1), "dd-mm-yyyy")
End Sub