Tags: app, application, date, excel, exporting, fields, formatting, microsoft, msdn, numberformat, numberformatlocal, object, range, software, spreadsheet, strcol

NumberFormat vs NumberFormatLocal

On Microsoft » Microsoft Excel

2,634 words with 1 Comments; publish: Fri, 23 May 2008 06:51:00 GMT; (30678.13, « »)

Hi, our application is exporting data to a spreadsheet. Using the

Excel.Application object. We are formatting the date fields using

app.Range(strCol & strRow).EntireColumn.NumberFormatLocal = "standard"

This usually works just fine, however sometimes some of the rows gets a

different format. E.g.

12.12.2003 Right aligned

12/12/2003 left aligned

Then I made this small macro that adds a comment with the NumberFormat and

NumberFormatLocal for the cells. And all the cells had

dd.mm.åååå In Numberformatlocal

and

d/m/yyyy in NumberFormat

So some of the rows were using NumberFormatLocal to format the dates while

some others were using NumberFormat. I then tried to change the format on

some the cells that were using just numberFormat, but it still showed in

the NumberFormat.

So I was wondering how does excel decide which format to use?

And does anyone know the values NumberFormatLocal can have?

Regards Martin

All Comments

Leave a comment...

  • 1 Comments
    • From someone who's never had to worry about international issues.

      I would think that by using the .numberformat, you wouldn't have to worry about

      all the codes that represent year (yyyy/jjjj/who the heck knows).

      I think xl will do the conversion when it hits the worksheet.

      (I've never seen a numberformatlocal of standard, but I have used

      numberformat="General", but that's not what you need with dates.)

      Martin V wrote:

      > Hi, our application is exporting data to a spreadsheet. Using the

      > Excel.Application object. We are formatting the date fields using

      > app.Range(strCol & strRow).EntireColumn.NumberFormatLocal = "standard"

      > This usually works just fine, however sometimes some of the rows gets a

      > different format. E.g.

      > 12.12.2003 Right aligned

      > 12/12/2003 left aligned

      > Then I made this small macro that adds a comment with the NumberFormat and

      > NumberFormatLocal for the cells. And all the cells had

      > dd.mm.åååå In Numberformatlocal

      > and

      > d/m/yyyy in NumberFormat

      > So some of the rows were using NumberFormatLocal to format the dates while

      > some others were using NumberFormat. I then tried to change the format on

      > some the cells that were using just numberFormat, but it still showed in

      > the NumberFormat.

      > So I was wondering how does excel decide which format to use?

      > And does anyone know the values NumberFormatLocal can have?

      > Regards Martin

      --

      Dave Peterson

      ec35720.excel.questionfor.info.msn.com

      #1; Fri, 23 May 2008 06:53:00 GMT