Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a date column which is from excell file where date is not in proper order like
DateCol:
APR
MAY
1997
36319
36901
18/11/2000
36893
14/10/2002
38821
39850
20/09/2009
40486
40278
2003
2001
2004
2005
2006
2009
2010
How to create it in a proper order.
Please help for this it is urgent
Thanks
Ritul
You should be able to create a solution using alt() function with some possible date interpretation functions.
You'll need to decide how to handle year numbers and months names, what date do you want to assign to these?
A solution could look like this:
Set DateFormat = 'DD/MM/YYYY';
SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';
LOAD *, alt(if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol)))) as NewDate INLINE [
DateCol
APR
MAY
1997
36319
36901
18/11/2000
36893
14/10/2002
38821
39850
20/09/2009
40486
40278
2003
2001
2004
2005
2006
2009
2010
];
Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )
please explain it.
Thanks,
Ritul
Just noticed, that you don't really need the alt() function here, reason being that you need to check the numeric value in a condition anyway:
if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol))) as DateColNew
Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )
please explain it.
If your numeric value is smaller than 2200, I assume it's describing a year (first argument to makedate() function), if it's larger than 2200, it's a date in numeric representation, so we just need to format the text representation.