#1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 1
Default 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




  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 420
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
.numberformat Q Aaron Excel Worksheet Functions 4 December 18th 09 03:15 AM
NumberFormat danpt Excel Discussion (Misc queries) 3 May 20th 09 10:55 PM
numberformat? Jack Sons Excel Discussion (Misc queries) 3 September 4th 07 03:44 PM
NumberFormat? alex Excel Worksheet Functions 3 March 1st 07 10:12 PM
using cells.numberformat maxzsim Excel Worksheet Functions 2 May 6th 05 10:41 AM


All times are GMT +1. The time now is 10:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"