How do I format a user defined function output as short date?

[jbenfleming]

I have worksheet function I made, Days90(). Its purpose is to output the date that is 90 days from the current day, not including weekends. Everything with it works except it is output as a serial instead of as a date. For example, if I use the function today it will output 43311 instead of 7/30/2018. Code:

Public Function Days90() As Date
Days90 = Date
If VBA.Format(Days90 + 90, "dddd") = "Saturday" Then
    Days90 = VBA.Format(Days90 + 92, "Short Date")
ElseIf VBA.Format(Date + 90, "dddd") = "Sunday" Then
    Days90 = VBA.Format(Days90 + 91, "Short Date")
Else
    Days90 = VBA.Format(Days90 + 90, "Short Date")
End If
End Function

I thought that the formatting of the result would show up on the worksheet. Any ideas?

 

[pjmorris]

hi, I think you would need to define the function as Public Function Days90() as String

That said, if you did do that the date would be useless elsewhere in your spreadsheet, you would be better to allow the return of the date as a serial number as you can then use it as the basis of other calculations. If you need it in short date format, simply format the cell in which the function is placed as a Short Date.

Hope that helps.

Regards

 

[JoeMo]

This puts Days90 in cell A1 with whatever format you set in the function.

Function Days90(Dte As Date) As Variant
Days90 = Dte + 90
Days90 = Format(Days90, "Short Date")  'change format to suit
End Function
Sub test()
[A1] = Days90(Date)
End Sub

 

[jbenfleming]

Defining as string did not work however you did give me the idea to define it as variant, which did the trick. Thanks!