NumberFormat property



From Access 03 I can change the NumberFormat Property of an Excel 03
cell. For cell say F10 if original Date format was "dd/mm/yyyy"
I can change it to "m/d/yyyy"
objResultsSheet.Range(F10).NumberFormat = "m/d/yyyy"

Do you know what are the repercussions of this change? The cell value
was originally 14/3/2011. It still is and this logical since my
Regional Settings have not changed.

In other words what is the use of the NumberFormat property?


Dave Peterson

Numberformat only affects numbers -- and dates/times are considered numbers in

If you changed the numberformat and the display didn't change, then that cell
didn't really contain a number (or date or time).

That means that the original value is text -- not a real date.

If you have a column of these text strings that you want to convert to dates,
you can:

Select the column
Data|Text to columns
choose date (dmy)
and finish up

Then try the numberformat change.

But be aware that you may have real dates in that column of dates/strings.

Could have been brought into your worksheet as January 2, 2003 or February 1,
2003 -- depending on how the data was imported and the short data format in
windows for that importer/user.

When I have to do this kind of stuff, I'll bring the values in as Text so excel
doesn't convert them to dates. Then I'd do the conversion and finally the

And I usually use an unambiguous date format (mmmm dd, yyyy) to check against
the original source. Then use the format I like after this check.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question