• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

....another silly udf question....

dan_l

dan_l

Active Member
So this UDF returns a date as a serial. Every time I run it over my data set, I just right click and format the cells as date, and go on with my pivot tables. That's fine, but I wonder if it's possible to return the value as a formatted date.


I tried format([serial],"MMDDYYYY"), but that bring it back as text (and the pivot table can't group those values.

[pre]
Code: 
Function YYYYMMDDcnv(target As Range)
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim dc As Date

year = Left(target, 4)
day = Right(target, 2)
month = Mid(target, 5, 2)

dc = DateSerial(year, month, day)

YYYYMMDDcnv = dc

End Function
[/pre]
 
Good day dan_I


I think your answer lies here http://www.techonthenet.com/excel/formulas/format_date.php


p.s.....In my book there are no silly questions, every one has a different level of knowledge, and learning, when you where born you could not drive a car, that did not make you "silly" you grew and learned.
 
Dan_L


I don't believe that Functions can change the format of the cell from which they are called


However as the cell is changing values, by the function, Excel will trigger the Worksheet Change event


So you can use that to pickup the Target and reset the format


So some code like:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Target.NumberFormat = "d/mm/yyyy;@"
End Sub
[/pre]
Will do it


Problem is that it will change any cell in the Worksheet to a date

So you might want to add another test inside the worksheet change event to check if the target is in a certain range or value etc
 
I would also change your function as


Code: 
Function YYYYMMDDcnv(target As Range) as Double
 
Bob,


I've messed with the format function: trouble it brings the value back as text which doesn't really play nice with pivot tables.


And you're probably right, but I"m still a n00b:)


Hui,

Can that be portable or do I have to put it into each workbook individually? Also, why the double?
 
Dates are Doubles

Without defining what it is it may end up as a Variant

Which Excel will interpret as a variant


You may find that even though the dates will now show up as 41234 etc that they should work in a Pivot table as they are now numbers


It would need to be in ever Worksheet Code Module that you want to use the function in
 
is there a function that will return the type of a variable? IE somefunction(myvariable) results as long, double, variant, whatever?
 
Try:

VarType(myvariable)


Read built in help or follow the link:

http://msdn.microsoft.com/en-IN/library/9w51ee9c%28v=vs.90%29.aspx
 
You must log in or register to reply here.
Back
Top