ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Setting up and Configuration of Excel (https://www.excelbanter.com/setting-up-configuration-excel/)
-   -   NumberFormat property (https://www.excelbanter.com/setting-up-configuration-excel/270657-numberformat-property.html)

JohnP[_2_]

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?

Thanks,
John





Dave Peterson[_2_]

NumberFormat property
 
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.

On 03/25/2011 03:43, JohnP wrote:
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





--
Dave Peterson


All times are GMT +1. The time now is 06:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com