Date formatting

[kjxavier]

1. I have posted a date to a field as January 05, 2008 and formatted it to be 01/05/2008 (mm/dd/yyyy) and result was good. but when i select the field the formula bar shows 1/5/2008 instead of 01/05/2008 removing the preceding 0's

Problem is when i take this excel sheet to compare with another sheet with field where i have directly entered date as 01/05/2008.

Result is : 1/5/2008 and 01/05/2008 and it says both fields are different.

Is there anyway i can solve this prob. as i told you before the formula bar displayed the 1st date from the beginning rather that 01/05/2008

2. The same thing happens when i posted Januray 05, 2008 and formatted it as Jan-08(Mmm-yy) result OK. but formula bar still showed the same 1/5/2008
and the same happens again when compared with the one originally entered as Jan-08

ALL I WANT IS THE FORMAT TO BE THE WAY I FORMAT IT (CONSTANT) FOREVER

 

[Rick Rothstein]

Dates are an odd entity. Excel stores dates as a floating point number... the integer portion represents the number of days since December 31, 1899 and the decimal portion is a fractional part of a 24-hour day represented by that time value. What you see in the Formula Bar and in the cell are strictly a convenience for your human eyes. So when you try to compare the text string "01/05/2008" to the Excel date 1/5/2008, what you actually end up comparing is "01/05/2008" to 39452 (which is how many days away from 12/31/1899 that date is). To do your comparison, you will have to convert one of the values to the other's format. How depends on if this is a worksheet function or VB question. Why don't you post your comparison formula or VB code line (what you thought should work) and let us modify it for you.

 

[kjxavier]

Dates are an odd entity. Excel stores dates as a floating point number... the integer portion represents the number of days since December 31, 1899 and the decimal portion is a fractional part of a 24-hour day represented by that time value. What you see in the Formula Bar and in the cell are strictly a convenience for your human eyes. So when you try to compare the text string "01/05/2008" to the Excel date 1/5/2008, what you actually end up comparing is "01/05/2008" to 39452 (which is how many days away from 12/31/1899 that date is). To do your comparison, you will have to convert one of the values to the other's format. How depends on if this is a worksheet function or VB question. Why don't you post your comparison formula or VB code line (what you thought should work) and let us modify it for you.

i am not allowed to attach files....... how do i send my files for your review

 

[kjxavier]

is there anyway i can send my files?

 

[Rick Rothstein]

i am not allowed to attach files....... how do i send my files for your review

I wasn't suggesting you attach your workbook, only show us the line you were trying to compare dates with. However, perhaps seeing your workbook might be more useful (just in case there are other formulas or code lines that might be involved in the process). How about uploading your workbook to one of the free file sharing websites on the Internet? You can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[kjxavier]

I wasn't suggesting you attach your workbook, only show us the line you were trying to compare dates with. However, perhaps seeing your workbook might be more useful (just in case there are other formulas or code lines that might be involved in the process). How about uploading your workbook to one of the free file sharing websites on the Internet? You can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

http://www.box.net/shared/peqketmyd4lzeiuag7gt

1. I have posted a date to a field as June 10, 2003 and formatted it to be 06/10/2003 (mm/dd/yyyy) and result was good. but when i select the field the formula bar shows 6/10/2003 instead of 06/10/2003 removing the preceding 0's

Problem is when i take this excel sheet to compare with another sheet with field where i have directly entered date as 06/10/2003.

Result is : 6/10/2003 and 06/10/2003 and it says both fields are different.

Is there anyway i can solve this prob.

2. The same thing happens when i posted November 03, 2009 and formatted it as Nov-09(Mmm-yy) result OK. but formula bar still showed the same 11/3/2009
and the same happens again when compared with the one originally entered as Nov-09

ALL I WANT IS THE FORMAT TO BE THE WAY I FORMAT IT (CONSTANT) FOREVER