Converting Dates

J

jer101

I have to perform a comparison with dates and I am having a problem wit
the format... I hope somebody can help me. Here's the proble
specifics...

The date format shows in the spreadsheet cell as 1-Jul-2003 but it i
really 7/1/2003 when I look up top. I need to know how to get conver
the 7/1/2003 to 1-Jul-2003 as a text format, if that is possible. The
I will be able to compare it to the date in my other spreadsheet whic
is in a text format.

:confused
 
S

Sébastien

Hi jer101,

To format the cells in that format:
-select the cells (or entire column)
-menu FOrmat > Cell. The Format Cells dialog displays.
-in the Number tab, for 'Category', choose 'Date', then choose the format from the 'type' list box.
-Click OK

Regards,
Sébastien
 
S

Sébastien

Hi jer101,

To format the cells in that format:
-select the cells (or entire column)
-menu FOrmat > Cell. The Format Cells dialog displays.
-in the Number tab, for 'Category', choose 'Date', then choose the format from the 'type' list box.
-Click OK

Regards,
Sébastien
 
J

JWolf

You can perform the comparison without doing anything.
If you have 1-Jul-2003 showing in a cell and 7/1/2003 showing in the
edit bar, then what you really have is the value 37803 stored in the
cell. Try formatting it as a number and you will see. Excel
automatically converts dates (when it thinks it sees you typing in a
date) to a serial number: the number of days since 01/01/1900 (1904 on a
few systems). So when you are doing a comparision of two dates, Excel
is really comparing two numbers. So, unless you specifcally formatted a
cell as text before you typed in 1-Jul-2003, Excel is treating the
number as 37803 with a cell format of dd-mmm-yyyy. If you want to
compare numbers it will be much easier to do it the way Excel was
designed, rather than try to trick it into using text for dates.

See Chip Pearson's Web site at: http://www.cpearson.com/excel/topic.htm
He has a number of useful pages about working with dates.
 
J

jer101

Thanks Fellas...

Sébastien,

I knew about your solution but it did not change the underlying format
and therefore, I could not use it in my comparison...

John,

Your solution worked like a charm...

Thanks again... this forum is awesome...

Ed Robinso
 
J

jer101

JWOLF...

Perhaps I did not explain my problem clearly in the earlier version..
(and I am not sure I will be any better this time) but of the dates
was comparing one was showing in a text format in both the cell and th
edit bar. The other was only showing in the text format in the cell.
Excel will not allow you to change a text version of a date to
numeric value using the format feature; so I had to change the numeri
value using John's solution.

I know I am new at this... but you guys are really helpful, I love thi
site...

Ed Robinso
 
Top