Converting Dates
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 |
Converting Dates
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. :confused: --- Message posted from http://www.ExcelForum.com/ . |
Converting Dates
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 |
Converting Dates
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 |
Converting Dates
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. :confused: --- Message posted from http://www.ExcelForum.com/ |
Converting Dates
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 |
Converting Dates
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 |
Converting Dates
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/ |
Converting Dates
|
All times are GMT +1. The time now is 03:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com