0

I wrote a function in excel vba. return value of the function is date, but function just return value of date in cell

how can I change format of cell to date (short date is preferred)

this is my code

Function ggg(rng As Range) As Date
ggg = rng.value - 466699
ggg = CDate(ggg)
End Function
Amir
  • 33
  • 7

2 Answers2

0

You cannot change format of the cell with the function. You either need a subroutine or function that returns string. For instance:

Function ggg(rng As Range) As String
    ggg = Format(rng.Value, "yyyy-mm-dd")
End Function
MarcinSzaleniec
  • 2,246
  • 1
  • 7
  • 22
  • thanks but if my function return string I can't use my date normally. for example I can't use date filter or I can't add or subtract some days to it – Amir Jan 10 '18 at 09:23
0

Right click on the sheet tab and choose "View Code". Paste below provided code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    For Each cel In Target.Cells
        If cel.HasFormula And (InStr(1, cel.Formula, "=ggg(", vbTextCompare) = 1) Then
            cel.NumberFormat = "dd-mm-yyyy"
        End If
    Next cel
End Sub

And see if it helps your cause or not!

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27