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

--
Message posted from http://www.ExcelForum.com

Have you tried using the text function - text(cell,"d-mmm-
yyyy")?

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

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




---
Message posted from http://www.ExcelForum.com/

.

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

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

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.

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

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




---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

Try DATEVALUE to convert a text date to numeric.

jer101 < wrote:

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 I
was comparing one was showing in a text format in both the cell and the
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 a
numeric value using the format feature; so I had to change the numeric
value using John's solution.

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

Ed Robinson


---
Message posted from http://www.ExcelForum.com/

Thanks... I'll try it..

--
Message posted from http://www.ExcelForum.com