Need help with dates conversion

[Ash_Maverick]

Hi Guys,

Please help me out with the attached file. The dates are in UK format and i just need the correct value to come out of them when I run a pivot. Somehow 10/8/2016 18:03 show me 8-Oct when I convert it.

[snb]

You are misusing the 'call in the cavalry' subforum.

You won't gain any friends, let alone help here doing that.

[AlanY]

try this

=DATE(YEAR(A2),DAY(A2),MONTH(A2))

[Ash_Maverick]

Thanks Alan but this is not working......... the dates highlighted in yellow are a bit different. This data is from a report which gets pulled from Sales Force. And sales force sucks big time in giving out raw data for dates.

Looks like the highlighted data is in some other format...........I don't know which.

[AlanY]

so, you've a mixed UK/US dates in Column A?
I can't see a solution, sorry.

[AlKey]

Hi Ash,
Please see attached file with corrected dates.
Formula used

Please Login or Register  to view this content.

[AlanY]

Hi Ash,
Please see attached file with corrected dates.
Formula used

Please Login or Register  to view this content.

the problem is how do we know 1/4/2016 is 1st April or 4th Jan?

[Ash_Maverick]

It works!!!!!...........Thank you Sir AlKey


Alan..........I suspect the problem is that some dates are like 12/7/2016 10:20:00 PM while some are like 13/07/2016 11:22............ wherever there is no AM/PM

[Ash_Maverick]

Wherever there is no AM/PM.......... the formula was giving error............. But AlKey's formula works fine with both kinds of dates/times.

[AlanY]

It works!!!!!...........Thank you Sir AlKey


Alan..........I suspect the problem is that some dates are like 12/7/2016 10:20:00 PM while some are like 13/07/2016 11:22............ wherever there is no AM/PM

are you sure it works?

first line of your sample post #4 10/8/2016 >>> 10/8/2016
second line 1/4/2015 >>> 4/1/2015

which one is correct?

[Ash_Maverick]

Hey Alan..........my apologies....... i am confused here..........in 4th post the dates in column A are in UK format and with your formula it gets converted to US format.....Its did not work for the ones highlighted in yellow.

[Ash_Maverick]

or maybe not.............. I just wanted it like this

[Winon]

I concur with AlanY,

I can't see a solution, sorry.

[Ash_Maverick]

Some clarity ??

[AlanY]

or maybe not.............. I just wanted it like this

If this is what you wanted,

try this

=DATE(YEAR(A2),Month(A2),DAY(A2))

[AlanY]

Or just format the column as dates in "dd-mmm-yyyy"

[Ash_Maverick]

Ok Alan.........your first formula works =DATE(YEAR(A2),DAY(A2),MONTH(A2)) with 'dd-mmm-yyyy' but it gives me #VALUE! wherever the data/time format is different from others(refer post# 8)

So I believe AlKey's formula is a modified version of your formula with IFERROR condition!!!