Two different date formats

[Anonymous]

Can someone please help on this?

I came across this question in one of the project interview:

I have a column A with two different date formats 'MM/DD/YYYY' and 'DD/MM/YYYY' loaded and the final output is one format 'YYYY/DD/MM'. How to solve this without using alt function?

Can we use something like Date(Date#(Date#(A,'MM/DD/YYYY'),'DD/MM/YYYY'),'YYYY/DD/MM')?

Thanks

[Anil_Babu_Samineni]

Perhaps this

Date(Date#(A),'yyyy/dd/mm')

[vishsaggi]

May be this:

Date(Floor(ColumnA), 'YYYY/DD/MM')

[Chanty4u]

try  below

alt( date#( A , 'MM/DD/YYYY' )
date#( A , 'DD/MM/YYYY' )
,'YYYY/DD/MM' )

[jonathandienst]

>>Can we use something like Date(Date#(Date#(A,'MM/DD/YYYY'),'DD/MM/YYYY'),'YYYY/DD/MM')?

No you can't. Alt is the correct way to solve this problem - Its a more complicated problem if you don't want to use Alt, so why would you want to avoid it?

=Date(Alt(Date#(A, 'MM/DD/YYYY'), Date#('DD/MM/YYYY')))

Change the order if you want DMY to take precedence over MDY for ambiguous dates.

[MarcoWedel]

maybe you can also use RangeMax or RangeMin instead of Alt.

hope this helps

regards

Marco

[ramchalla]

Hi Karthick,

I think this will help you.

T1:
LOAD Dates
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);

LOAD
Date(Dates,'YYYY/DD/MM') as FormattedDates
Resident T1;