change cell format within functions

[amiroo]

Hi,

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 Gregorian(rng As Range, Optional Mode As Boolean = False) As Date
DateArr = separate_date(rng.value)  [COLOR=#339900]' such as: 1396/02/19 (jalali date)[/COLOR]
If UBound(DateArr) <> 2 Then Gregorian = CVErr(2042)
Gregorian = Date2Value(rng.value, "J") - 466699  [COLOR=#339900]' such as: 509806 - 466699 => return 43107[/COLOR]
End Function

 

[JLGWhiz]

You could try adding this line

Gregorian = CDate(Gregorian)

It is difficult to do a good analysis when all relevant code is not posted. There appears to be two other macros used by your function, which are not posted in the thread.

 

[amiroo]

You could try adding this line

Gregorian = CDate(Gregorian)

It is difficult to do a good analysis when all relevant code is not posted. There appears to be two other macros used by your function, which are not posted in the thread.

thanks but didn't worked
that two function have many function inside. it's complicated
I tried this simple function too. but it doesn't work too. check it out please

Function ggg(rng As Range) As Date
ggg = rng.value - 466699
ggg = CDate(ggg)
End Function

 

[JLGWhiz]

Your destination range needs to be formatted as 'Date' also. If it is posting as the date value, then it is apparently formatted as general or number. Since you are using the UDF as a worksheet function, it might be difficult to determine in advance which cell needs to be formatted, unless you always use the same column or row. I don't really have any other solutions to offer at this point.
Regards, JLG

 

[amiroo]

Your destination range needs to be formatted as 'Date' also. If it is posting as the date value, then it is apparently formatted as general or number. Since you are using the UDF as a worksheet function, it might be difficult to determine in advance which cell needs to be formatted, unless you always use the same column or row. I don't really have any other solutions to offer at this point.
Regards, JLG

thanks for reply but I can't change format of cell. I want my function do this like TODAY() or NOW() functions


when I wrote this function I solved this problem by change output format of function to date (as Date at the end of define function line), after a while I had changed some part of codes and then it didn't work again. unfortunately I don't remember what was my changes


by the way, thanks for your attention

 

[amiroo]

any solution?

 

[Jaafar Tribak]

Maybe :

Function ggg(rng As Range) [COLOR=#0000ff]As String
[/COLOR]    ggg = rng.Value - 466699
    ggg =[COLOR=#0000ff] Format(ggg, "yy/mm/dd hh:mm:ss")[/COLOR]
End Function

 

[amiroo]

Maybe :

Function ggg(rng As Range) [COLOR=#0000ff]As String
[/COLOR]    ggg = rng.Value - 466699
    ggg =[COLOR=#0000ff] Format(ggg, "yy/mm/dd hh:mm:ss")[/COLOR]
End Function

thanks but this code return a string in cell so we can not use it as date. for example in date filter we don't have group of year and month

I just need change format of cell. I think it should be possible

 

[Jaafar Tribak]

thanks but this code return a string in cell so we can not use it as date. for example in date filter we don't have group of year and month

I just need change format of cell. I think it should be possible

The date values DO show in the filter for me .

Not sure but would it work for you if you add a space before the Format function as follows :

Function ggg(rng As Range) As String
    ggg = rng.Value - 466699
    ggg = " " & Format(ggg, "yy/mm/dd hh:mm:ss")
End Function

 

[amiroo]

The date values DO show in the filter for me .

Not sure but would it work for you if you add a space before the Format function as follows :

Function ggg(rng As Range) As String
    ggg = rng.Value - 466699
    ggg = " " & Format(ggg, "yy/mm/dd hh:mm:ss")
End Function

yes it shows but don't group it and you can't use date filters either. take a look at this pic: