Regarding Date Format

[Allwinv]

Hi Excel Experts,

In one of the Cell, i have the data as 13/6/2018 2:38 PM. But i want to make this data as 13/06/2018. I entered formula as =DATE(YEAR(A1),MONTH(A1),DAY(A1)). But i am getting the error due to AM and PM available in cell. I removed AM and PM in the cell (using the replace Option) and applied the formula as =DATE(YEAR(A1),MONTH(A1),DAY(A1)). But still not working for the cells which are AM and PM data.

Can you please help.

Thanks.

 

[Rick Rothstein]

Hi Excel Experts,

In one of the Cell, i have the data as 13/6/2018 2:38 PM.

Is that a text or a real date?

Is that a result of a formula?

 

[Allwinv]

Is that a text or a real date?

Is that a result of a formula?

Hi,

I just downloaded the data from sales force to Excel. After importing the data, I need to format the date fields from the cells.

I feel that is the result data. Please suggest.

 

[Aryatect]

Hi, in case if it is already in the date format, you can just number format it for d/m/yyyy. If you need to perform some additional steps on this date then you can ROUNDDOWN() the date then change the number format d/m/yyyy format.

If it is stored as text and if the date format is right for your excel then DATEVALUE() should work directly then number formatting this field d/m/yyyy

 

[Rick Rothstein]

I just downloaded the data from sales force to Excel. After importing the data, I need to format the date fields from the cells.

From your description, you might have text in your cells, not real dates. Let's test it. Put this formula in an empty cell...

=ISNUMBER(A1)

but change the A1 to a cell address containing one of your dates, then tell us what the formula displayed.

 

[Allwinv]

Thank you for the reply.

I just keep the data as 13/6/2018 2:38 PM in A1 cell and applied this formula =ISNUMBER(A1). It is giving the result as FALSE

I modified the the date as 13/6/2018 in A2 cell and applied this formula =ISNUMBER(A1). It is giving the result as FALSE only.

Pls suggest.

 

[Aryatect]

Thank you for the reply.

I just keep the data as 13/6/2018 2:38 PM in A1 cell and applied this formula =ISNUMBER(A1). It is giving the result as FALSE

I modified the the date as 13/6/2018 in A2 cell and applied this formula =ISNUMBER(A1). It is giving the result as FALSE only.

Pls suggest.

Is the data in the correct excel format ? as in you are using d/m/yyyy, does your data when typing in TODAY() formula also comes in the same format ?

 

[Allwinv]

Hi,

As i checked the format by typing today(), it is mm/dd/yyyy.

But as i checked the other cell of data, it is dd/mm/yyyy.

Thanks.

 

[Allwinv]

I just tried with formula istext(A1) and the vaue returns as TRUE

 

[Aryatect]

So I think this is the issue, your excel is m/d/yyyy and this data is in d/m/yyyy. Confirm if this the case then do below:

1. Use this to extract date (=LEFT(K4,SEARCH(" ",K4)-1)).
2. Use this thread to change the d/m/yyyy to m/d/yyyy

https://www.mrexcel.com/forum/excel-questions/1103741-formula-convert-american-date-uk-date.html