IIf Statement Looking at Months

Question

I would like to create an IIf statement to calculate when the expected leaving date of a child is.

For example, a child who is born before 31/08 is expected to leave nursery after 4 years, a child who is born after that date is expected to leave after 5 years.

Now what I was trying to do is ask an IIF statement which looks at the date of birth and decides whether to calculate for 4 years or for 5 years. However I keep running into issues with the code that I am using which is

= IIf([Date of Birth]>#31/08/0000# , =DateAdd("yyyy",4,[Date of Birth]) , =DateAdd("yyyy",5,[Date of Birth]))

as there are multiple children with different dates of birth. There needs to be a way to look specifically at the months only.

EDIT: Turns out that is not what my boss needs, what he needs is basically to display when the child is leaving from the nursery i.e. when the new school term rolls around and the child is 4 years old. if the child is born before September he is applicable to start school that year. if he isn't the child is applicable to start school the next year on the month of September. And right now I have no idea what to do as my attempts of doing an IIF function have completely failed. Can anyone Help?

Answer 1

Try with:

=DateAdd("yyyy", IIf([Date of Birth] > DateSerial(Year([Date of Birth]), 8, 31), 4, 5), [Date of Birth])

Edit 1:

You can use DateAdd like:

=IIf(DateAdd("yyyy", 4, [Date of Birth]) < DateSerial(Year(Date()), 9, 1), "Start school this year", "Postpone school start")

Edit 2:

Or you could calculate the age of the children on September 1st:

AgeAtSeptember: Age([Date of Birth], DateSerial(Year(Date()), 9, 1))

using this function:

' Returns the difference in full years from DateOfBirth to current date,
' optionally to another date.
' Returns zero if AnotherDate is earlier than DateOfBirth.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   any date/time value of data type Date
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28th when adding a count of years to dates of Feb. 29th
' when the resulting year is a common year.
'
' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Age( _
    ByVal DateOfBirth As Date, _
    Optional ByVal AnotherDate As Variant) _
    As Integer

    Dim ThisDate    As Date
    Dim Years       As Integer

    If IsDateExt(AnotherDate) Then
        ThisDate = CDate(AnotherDate)
    Else
        ThisDate = Date
    End If

    ' Find difference in calendar years.
    Years = DateDiff("yyyy", DateOfBirth, ThisDate)
    If Years > 0 Then
        ' Decrease by 1 if current date is earlier than birthday of current year
        ' using DateDiff to ignore a time portion of DateOfBirth.
        If DateDiff("d", ThisDate, DateAdd(IntervalSetting(DtInterval.dtYear), Years, DateOfBirth)) > 0 Then
            Years = Years - 1
        End If
    ElseIf Years < 0 Then
        Years = 0
    End If

    Age = Years

End Function