Date Field Format Detection

[Gives]

How can I detect the Date format of a given cell?

I have a macro to create pivot tables from a column data in a spreadsheet. The column titles are used in data validation list on a separate spreadsheet. The user selects which fields to use as rowfields, columnfields, pagefields, and datafields from the validation drop-down lists. Some columns have dates as the column title; Actual entry is 1/1/2009 but formating changes to Jan-09. The validation list displays Jan-09 in the worksheet and 1/1/2009 in the fx toolbar. I use activecell.offset.value to get the validation list field which retrieves 1/1/2009. The With pivotfields(activecell.offset.value) statement fails because it can't find 1/1/2009. When I substitute Jan-09 in the pivotfields statement it works fine. So.....

How can I detect the original Date format in the Table and then set activecell.offset.value to that format so it will find the column?

 

[VoG]

You can get the format like this

MsgBox ActiveCell.NumberFormat

 

[Gives]

Thanks. That's exactly what I needed!