Advanced age display from birthday

[Marrows]

Hi I work on a paediatric ward in a hospital and my boss has set me a challenge for our handover sheet.
If I enter the birthday of a child, I can have the age displayed as 1y10m6d for eg.
What we want is if the child is younger than 1 to have just 2m5d displayed for eg.
If aged 1-3 have 1y4m displayed for eg.
and older than 3 just have 6y displayed for eg.


Is this possible? I have no idea where to start!

 

[Rick Rothstein]

Hi I work on a paediatric ward in a hospital and my boss has set me a challenge for our handover sheet.
If I enter the birthday of a child, I can have the age displayed as 1y10m6d for eg.
What we want is if the child is younger than 1 to have just 2m5d displayed for eg.
If aged 1-3 have 1y4m displayed for eg.
and older than 3 just have 6y displayed for eg.

Is this possible? I have no idea where to start!

Give this UDF (user defined function) a try...

Function YMD(ByVal StartDate As Date, _
             Optional ByVal EndDate As Variant, _
             Optional LeapDayInNonLeapYearIsMar1 As Boolean = True) As String
  Dim TempDate As Date, NumOfHMS As Double
  Dim NumOfYears As Long, NumOfMonths As Long, NumOfWeeks As Long, NumOfDays As Long
  StartDate = Int(StartDate)
  If IsMissing(EndDate) Then
    Application.Volatile
    EndDate = Date
  Else
    EndDate = Int(EndDate)
  End If
  If Not LeapDayInNonLeapYearIsMar1 And IsDate("2/29/" & Year(StartDate)) Then
    If Format(StartDate, "m/d") = "2/29" Then StartDate = StartDate - 1
  End If
  NumOfYears = DateDiff("yyyy", StartDate, EndDate)
  StartDate = DateSerial(Year(EndDate), Month(StartDate), Day(StartDate))
  If StartDate > EndDate Then
     StartDate = DateAdd("yyyy", -1, StartDate)
     NumOfYears = NumOfYears - 1
  End If
  NumOfMonths = DateDiff("m", StartDate, EndDate)
  StartDate = DateSerial(Year(EndDate), Month(EndDate), Day(StartDate))
  If StartDate > EndDate Then
     StartDate = DateAdd("m", -1, StartDate)
     NumOfMonths = NumOfMonths - 1
  End If
  NumOfDays = Abs(DateDiff("d", StartDate, EndDate))
  If NumOfYears < 1 Then
    YMD = CStr(NumOfMonths) & "m" & CStr(NumOfDays) & "d"
  ElseIf NumOfYears < 4 Then
    YMD = CStr(NumOfYears) & "y" & CStr(NumOfMonths) & "m"
  Else
    YMD = CStr(NumOfYears) & "y"
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use YMD just like it was a built-in Excel function. For example,


=YMD(A1,A2)

 

[Marrows]

THANKYOU THANKYOU THANKYOU!!! My husband is very good with Excel and even he can't believe this could be done! It works perfectly!
Cheers, Marie