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"
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
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
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
Sub M_snb()
MsgBox Format(DateSerial(Year(Date) + (Month(Date) < 4), 4, 1), "dd-mm-yyyy")
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.