Unable to change Date format

[Kyrene7]

Hello,

I have this stupid problem where I cannot change the date formatting. Can someone help me.

I'm going crazy... I check in google to see if I can solved my problem. I could see a lot of people has a trouble change date format but it doesn't solved mine.

Here is what I already did.

I copy a lot of data from several Excel.CSV. I can send an Excel with my problem.

No Formula.

column A, I could change the date formatting but now I can't!
column B, some date I could change and some not.

I first separeted the date and time, it works but change the date formatting, isn't working
then save my work in HTML but it isn't working,
then save in CSV, it isn't working.
Copy and paste and Notepad, isn't working.
Clear all formating, isn't working
No macro are visible.

Well can someone has a new idea to help me out, I'm out of idea

Please ask me the excel sheet and I will send it to you.

Thank you

Kyrene

 

[Special-K99]

You'd do better describing what the data looks like, and what you want it to look like than what you did.

 

[Kyrene7]

You'd do better describing what the data looks like, and what you want it to look like than what you did.

It is better to see in excel and you will understand.

I just need to be able to change the date format on any kind of date format. Some cell are working and the date are on the right side and the one I can't change are on the left side.

For the moment the cell are kind of blocked and I can't choose any of the date format.

I can send the excel sheet to you and it will be easier to understand my problem.

 

[Kyrene7]

It is better to see in excel and you will understand.



I just need to be able to change the date format on any kind of date format. Some cell are working and the date are on the right side and the one I can't change are on the left side.

For the moment the cell are kind of blocked and I can't choose any of the date format.

I can send the excel sheet to you and it will be easier to understand my problem.

If I delete the date and rewrite in it, it is working... I'm seriously going nuts.
I have more than 4000*2 cells to change.
Copy paste doesn't change anything

 

[netuser]

Upload somewhere your excel file and share it so we can look in to it.

Can you try one thing place cursor at the end of your data in a cell that is not working and press enter and see what happens. This usually solve my issue where excel is stubborn to consider the data as Text even though you have as numeric or date ...

 

[Kyrene7]

Upload somewhere your excel file and share it so we can look in to it.

Can you try one thing place cursor at the end of your data in a cell that is not working and press enter and see what happens. This usually solve my issue where excel is stubborn to consider the data as Text even though you have as numeric or date ...

Thank you netuser, I tried what you said but it is not working

here is the link to the file., let me know if it doesn't work. You will see you will go crazy.

https://drive.google.com/file/d/0B3q-Wr3tr7QzTFo4TTgycUxMRFE/view?usp=sharing

 

[netuser]

Is it Column B ? If yes I think we have to convert them to date. I have French excel and it would have normaly shows date as YYYY-MM-DD but it is not doing it. I think excel is considering it as Text.

We can easily create the date in Column C, by using this formula :

Excel 2010 32 bit

	A	B	C
1	notification date	acknowledgement	Date fixed
2	31-Aug-15	8/31/2015	08/31/2015
3	10-May-16	8/26/2015	08/26/2015
4	5-Dec-12	8/26/2015	08/26/2015
5	30-Aug-12	7/23/2015	07/23/2015

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IFERROR(DATE(RIGHT(B2,4),LEFT(B2,SEARCH("/",B2,1)-1),MID(B2,SEARCH("/",B2,1)+1,2)),B2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[MARZIOTULLIO]

Hi

try

c2=VALUE(A2) copy down . After format as date

 

[Kyrene7]

Is it Column B ? If yes I think we have to convert them to date. I have French excel and it would have normaly shows date as YYYY-MM-DD but it is not doing it. I think excel is considering it as Text.

We can easily create the date in Column C, by using this formula :

Excel 2010 32 bit

	A	B	C
1	notification date	acknowledgement	Date fixed
2	31-Aug-15	8/31/2015	08/31/2015
3	10-May-16	8/26/2015	08/26/2015
4	5-Dec-12	8/26/2015	08/26/2015
5	30-Aug-12	7/23/2015	07/23/2015

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IFERROR(DATE(RIGHT(B2,4),LEFT(B2,SEARCH("/",B2,1)-1),MID(B2,SEARCH("/",B2,1)+1,2)),B2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you so much for your time on my problem.

I actually solved by doing text to colums and divided it by numbers then put it back with the formula =date(year;month;day) and it workef

 

[Kyrene7]

Hi

try

c2=VALUE(A2) copy down . After format as date

Thank you for your help.