Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I am working on an excel sheet with different columns for a date , ie Year, month, day.
LOAD Year,
Month,
Day,
Hour,
Celsius,
Rain_mm,
Sun_Minutes,
MakeDate (Day&Month&Year) as Date
i have been trying using MAKEDATE to generate the date but in returns...i have this...01.01.112012, 01.01.122012, etc...
what i have done wrong ?
thanks
JP
Hi,
If you want change the format use Date() as suggested by Swehl
like
Date(MakeDate(Year,Month,if(isnull(Day) or len(trim(Day))<1 ,'1',Day)),'DD/MMM/YYYY')
Regards
Year | Month | Day | Hour | Celsius | Rain_mm | Sun_Minutes |
2012 | 1 | 1 | 0 | 5.6 | 0 | 0 |
2012 | 1 | 1 | 1 | 5.9 | 0 | 0 |
2012 | 1 | 1 | 2 | 5.8 | 0 | 0 |
2012 | 1 | 1 | 3 | 5.7 | 0 | 0 |
2012 | 1 | 1 | 4 | 5.4 | 0 | 0 |
2012 | 1 | 1 | 5 | 5.1 | 0 | 0 |
2012 | 1 | 1 | 6 | 5.5 | 0 | 0 |
2012 | 1 | 1 | 7 | 5.8 | 0.1 | 0 |
2012 | 1 | 1 | 8 | 6.7 | 0 | 0 |
2012 | 1 | 1 | 9 | 7.6 | 0.1 | 0 |
2012 | 1 | 1 | 10 | 8.5 | 0 | 0 |
2012 | 1 | 1 | 11 | 9.5 | 0 | 0 |
2012 | 1 | 1 | 12 | 10.2 | 0 | 1 |
2012 | 1 | 1 | 13 | 10.4 | 0 | 0 |
2012 | 1 | 1 | 14 | 10.8 | 0 | 0 |
2012 | 1 | 1 | 15 | 10.6 | 0 | 0 |
2012 | 1 | 1 | 16 | 9.5 | 0 | 0 |
2012 | 1 | 1 | 17 | 8.6 | 0 | 0 |
2012 | 1 | 1 | 18 | 7.9 | 0 | 0 |
2012 | 1 | 1 | 19 | 8 | 0 | 0 |
2012 | 1 | 1 | 20 | 7.3 | 0 | 0 |
2012 | 1 | 1 | 21 | 6.3 | 0 | 0 |
2012 | 1 | 1 | 22 | 6.1 | 0 | 0 |
2012 | 1 | 1 | 23 | 7.1 | 0 | 0 ! |
this is Jan 1 2012 full day, information on weather forecast hour by hour...
And need to transform this excel sheet in order to get this result : Day Month Year 01.01.2012
Hi,
What is your expected output????
Regards
LOAD *,
Date(MakeDate (Year,Month,Day),'DD.MM.YYYY') as Date
//MakeDate(Year, Month,Day) AS Date
INLINE [
Year,Month,Day,Hour,Celsius,Rain_mm,Sun_Minutes
2012,1,1,0,5.6,0,0
2012,1,1,1,5.9,0,0
2012,1,1,2,5.8,0,0
2012,1,1,3,5.7,0,0
2012,1,1,4,5.4,0,0
2012,1,1,5,5.1,0,0
2012,1,1,6,5.5,0,0
2012,1,1,7,5.8,0.1,0
2012,1,1,8,6.7,0,0
2012,1,1,9,7.6,0.1,0
2012,1,1,10,8.5,0,0
2012,1,1,11,9.5,0,0
2012,1,1,12,10.2,0,1
2012,1,1,13,10.4,0,0
2012,1,1,14,10.8,0,0
2012,1,1,15,10.6,0,0
2012,1,1,16,9.5,0,0
2012,1,1,17,8.6,0,0
2012,1,1,18,7.9,0,0
2012,1,1,19,8,0,0
2012,1,1,20,7.3,0,0
2012,1,1,21,6.3,0,0
2012,1,1,22,6.1,0,0
2012,1,1,23,7.1,0,0
];
One column with the date in the following format 01.01.2012 (day, month. year) and this result for each line of my weather forecast.
thanks for your support
As already suggested, I'd use Date(MakeDate(Year, Month,Day),'DD.MM.YYYY') AS Date.
I understood that's what you already get?
try tis way,
MakeDate (Day(Day)&Month(Month)&Year(Year)) as Date --i think it makes difference.
something like tihs exactly,
Makedate(Year([Order Date]),Month([Order Date]),Day([Order Date])) as DayMonthYear,