Date and time format issue
- Thread starter Ram Mher
- Start date
xlstime
Active Member
Hi Ram,
If this is the case (as mentioned in excel), you can just add 0 to get proper formate.
If this is the case (as mentioned in excel), you can just add 0 to get proper formate.
NARAYANK991
Excel Ninja
Hi ,
The two values are not the same.
One is the date corresponding to August 5 , while the other is a date corresponding to May 8.
Just placing the cursor in cell A2 , pressing F2 and then ENTER converts the data to a date value.
Can you try this and see if the two date values are the same ?
Use a format of GENERAL , so that a direct comparison is possible ; the top value displays 42587.2769675926 while A2 displays 42498.2769675926
Narayan
The two values are not the same.
One is the date corresponding to August 5 , while the other is a date corresponding to May 8.
Just placing the cursor in cell A2 , pressing F2 and then ENTER converts the data to a date value.
Can you try this and see if the two date values are the same ?
Use a format of GENERAL , so that a direct comparison is possible ; the top value displays 42587.2769675926 while A2 displays 42498.2769675926
Narayan
Marc L
Excel Ninja
Hi !
On my side with Europa regional setting, dates are the same ! (August 5)
But only the first one is formatted as a date, the second is not formatted
(as General) but understood as a text when I swap display to formula mode
(Ctrl 3 on alphanumeric keyboard) !
If I edit the second cell and just valid with enter key, the cell becomes
a real date and is no more understood as a text (date formatted) …
Bad entry, bad Excel behavior !
So the question is how this cell was created ?
I suppose this is a classic bad importation, no ?
On my side with Europa regional setting, dates are the same ! (August 5)
But only the first one is formatted as a date, the second is not formatted
(as General) but understood as a text when I swap display to formula mode
(Ctrl 3 on alphanumeric keyboard) !
If I edit the second cell and just valid with enter key, the cell becomes
a real date and is no more understood as a text (date formatted) …
Bad entry, bad Excel behavior !
So the question is how this cell was created ?
I suppose this is a classic bad importation, no ?
Dear Narayan thanks for the support.
i placed the cursor in cell A2 and then value of cell A2 have been converted as below:-
Cell A1-05/08/2016 6:38 AM
Cell A2- 5/8/2016 6:38
Please suggest another method as it is not being possible to place the cursor in each cell manually . i have around 10000 rows in my sheet.
Marc L
Excel Ninja
NARAYANK991
Excel Ninja
Hi ,
As I have already posted , on my computer , the two items of data appear to be the same , but when converted to numbers , are shown to have two different values.
If you just want to convert all your values to numbers , select the entire range of values ( all 10000 rows in one column ) , and click on Text to Columns.
Click on Next , Next and then select DMY or MDY , depending on what your system date format is.
Narayan
As I have already posted , on my computer , the two items of data appear to be the same , but when converted to numbers , are shown to have two different values.
If you just want to convert all your values to numbers , select the entire range of values ( all 10000 rows in one column ) , and click on Text to Columns.
Click on Next , Next and then select DMY or MDY , depending on what your system date format is.
Narayan
dear Narayna
i tried through text to columns and selected both DMY and MDY option but instead of reflecting 5 aug 16 it is reflecting 8 may 16 . Please provide the excel sheet in which you have done this work.
xlstime
Active Member
NARAYANK991
Excel Ninja
Hi ,
I think you should re-read my first post , which clearly states that though the two cells appear to be having the same value , in reality they are different.
There is no way to change the second value to the first , unless you use a formula or VBA code.
Narayan
I think you should re-read my first post , which clearly states that though the two cells appear to be having the same value , in reality they are different.
There is no way to change the second value to the first , unless you use a formula or VBA code.
Narayan
Dear Narayan thanks a lot for resolving the issue
my computer date was in MM/DD/yy format so i changed it in DD/MM/YY. lastly i made both values as DMY by using text to column and got the exact result.