Please help me with a simple VBA function

[laurieb]

Hi! I'm trying to write a piece of code to calculate the gestational age of expectant mothers based on their estimated due date. This is a first attempt at making a user defined function in excel and I can't seem to figure out why it won't work. Any help you can give would be much appreciated.

Thank you!
Laurie

Function GA(edc As Date, dd As Date, today As Date) As String
'Gestational Age (GA)
If IsMissing(today) Then
today = Date
End If
If IsMissing(edc) Then
GA = ""
Else
Select Case dd
Case IsMissing
GA_Days_Total = 280 + Int(edc - today)
Case Is > today
GA_Days_Total = 280 + Int(edc - dd)
Case Is = today
GA_Days_Total = 280 + Int(edc - dd)
End Select

GA_Weeks = Fix(GA_Days_Total / 7)
GA_days = GA_Days_Total Mod 7

GA = CStr(GA_Weeks) & " " & CStr(GA_days) & "/7 weeks"
End If

End Function

[Barrie Davidson]

How about this (note that the inputs are dates entered as yyyymmdd - so October 1, 2006 would be 20061001):

Function GA(edc As String, dd As String, today As String) As String
'Gestational Age (GA)


If IsMissing(today) Then
    today = Format(Now, "yyyymmdd")
End If
If IsMissing(edc) Then
    GA = "n/a"
Else
    Select Case DateSerial(Left(dd, 4), Mid(dd, 5, 2), Right(dd, 2))
        Case Is = ""
            GA_Days_Total = 280 + Int(DateSerial(Left(edc, 4), Mid(edc, 5, 2), Right(edc, 2)) _
                - DateSerial(Left(today, 4), Mid(today, 5, 2), Right(today, 2)))
        Case Else
            GA_Days_Total = 280 + Int(DateSerial(Left(edc, 4), Mid(edc, 5, 2), Right(edc, 2)) _
                - DateSerial(Left(dd, 4), Mid(dd, 5, 2), Right(dd, 2)))
    End Select
End If

GA_Weeks = Fix(GA_Days_Total / 7)
GA_days = GA_Days_Total Mod 7

GA = Format(GA_Weeks, "0") & " " & Format(GA_days, "0") & "/7 weeks"

End Function

Does this help?

[laurieb]

For some reason I can't get it to work. Did it work for you?

Thanks for your help!
Laurie

[MrShorty]

In what way is it not working? Error messages? Returning the wrong answer?

[laurieb]

It gives me a #name error. Not sure why.

Thanks again for the help everyone.
Laurie

[laurieb]

Also, I'd really like to keep the date in date format on my spreadsheet.

Thanks!