NumberFormat property


J

JohnP

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?

Thanks,
John
 
Ad

Advertisements

D

Dave Peterson

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

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.

01/02/2003
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
numberformatting.

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

Top