Solved Struggling with Date Formats in Excel 2007

Microsoft Microsoft office excel 2007 ac...
August 3, 2011 at 09:32:56
Specs: Windows XP
I have imported data from Peachtree into Excel. My date formats look like 1/1/2010, 2/3/2010 and 3/22/1010. I need to group the data by month. However, when I use the =month() function Excel returns 1/1/1900, 1/2/1900 and 1/3/1900, respectively. I've tried copying the dates and pasting them back as values, but I get the same outcome. What should I do?

See More: Struggling with Date Formats in Excel 2007

August 3, 2011 at 09:54:10
✔ Best Answer
MONTH() will return 1/1/1900, 1/2/1900, etc. if the cell that the MONTH() function is in is formatted as a Date.

Excel stores dates and times internally as numbers, with the Integer portion being the date and the decimal portion being the time (stored as the decimal portion of 24hrs).

Since the Excel calendar starts on 1/1/1900, if the MONTH() function returns 1 and you format that cell as a date, it will return the date for Day 1 or 1/1/1900.

Try this: Enter a 2 in any cell and format as a date. You should get 1/2/1900 since that is the date for Day 2. Thus if any function returns a numerical value and you format it as a Date or Date & Time, Excel will return the equivalent Date/Time for that number.

Change the formatting of the cell in which you are using the MONTH() function and you should no longer get 1/1/1900, etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 3, 2011 at 10:02:55
Worked! Thanks.

Report •
Related Solutions

Ask Question