Convert serial date to day format

[YasserKhalil]

Hello
I have this function which returns the first day of any month
it returns the serial number for that date

Please Login or Register  to view this content.

I know how to format cell in excel by Custom then ddd
or I can edit the formula to be like that:

Please Login or Register  to view this content.

But I can't edit the function to be able to get the desired result
I tried this line

Please Login or Register  to view this content.

but i got value error

[TMS]

Try:

Please Login or Register  to view this content.

Regards, TMS

[ExcelClout]

Are you trying to call the Text function in VBA?

Please Login or Register  to view this content.

[jindon]

What do you want the function to return?
You declared the return data type as Date, but the last line gives you String type.

Please Login or Register  to view this content.

[shg]

Or similarly,

Please Login or Register  to view this content.

Or you could return a date and then format as ddd:

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much
The problem was in variable As Date
Just a question. when declared As Date i got serials not dates .. how to convert these serials to dates

[jindon]

If you are using as formula in cell, change the cell format to date.

[YasserKhalil]

I merged two functions one for first day and one for last day

Please Login or Register  to view this content.

What can I do to get blank results if the cell value is blank?
I tried this line at first but got value errors

[jindon]

=if(a1<>"",FirstLastDay(a1),"")
?

[YasserKhalil]

I fixed it

Please Login or Register  to view this content.

But I need another criterion for text entries
i tried Istext(dtmDate) but got error at the function

[jindon]

I give up.....

[YasserKhalil]

I give up.....

I followed your replies and they are great in fact.
But I want the function itself to be edited to get my own results as I wish.
Generally thanks for all valuable information you provided

[YasserKhalil]

OK any suggestion about that

Please Login or Register  to view this content.

[karedog]

Hi,

Maybe this ?

Please Login or Register  to view this content.

Regards