Facing issue when NumberFormat is set as "MM/dd/yyyy"

Question

0.00/5 (No votes)

See more:

Hi,

I'm generating an Excel file using Microsoft.Office.Interop.Excel dll. I have an issue when I set the NumberFormat as "MM/dd/yyyy". When the date is less than 1900 it throws an exception.

I could understand that the DateTime format allows the column to hold date till 1900. Now how shall I change the format to hold date less than 1900?

Please help.

Thanks,
Srikanth

Posted 15-Dec-10 0:00am

sri_3464

Updated 15-Dec-10 0:07am

Slacker007

v2

Add a Solution

Comments

fjdiewornncalwe 15-Dec-10 14:34pm

Why would you need the spreadsheet to hold dates earlier than 1900? If it is a static field, you could also perform a transformation and store the value as a string value column in the format you want instead of as a number format.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2010-12-15T00:09:00

Maybe Excel doesn't support dates earlier than 1900... have you googled it?

EDIT ==============

I googled it, and found out that the earliest possible date Excel will accept is 01/01/1900. You're out of luck, Sparky. Here's the link I found the info at:

http://www.ozgrid.com/Excel/free-training/excel-lesson-13-basic.htm[^]

Scroll down to the section labeled "Excel Dates and Times".

T2102 · Answer 2 · 2010-12-20T03:11:00

Solution 3

You probably should not use a date before 1900 anyway and Excel mistakenly thinks 1900 is a leap year, so the date serial is off that year anyway.

Posted 20-Dec-10 3:11am

T2102