I have an Excel sheet with the columns FROM_DATE and TO_DATE which keep date in text format.
I wrote script to convert them as date time format and to find their interval. However, when load there is an error of "Field not found - <F_TO_DATE> returned.
[Excel Sheet A]:
DATE(DATE#([FROM_DATE],'MMDDYY'),'MM/DD/YYYY') AS [F_FROM_DATE],
DATE(DATE#([TO_DATE],'MMDDYY'),'MM/DD/YYYY') AS [F_TO_DATE],
INTERVAL([F_TO_DATE] - [F_FROM_DATE], 'D') AS [WORK_EXP_DAY]
// NUM([WORK_EXP_DAY]/30, '#,##0.0') AS [WORK_EXP_MONTH],
// NUM([WORK_EXP_DAY])/365, '#,##0.0') AS [WORK_EXP_YEAR]
(ooxml, embedded labels, table is [Excel Sheet A]);
If the interval line was changed as this INTERVAL([TO_DATE] - [FROM_DATE], 'DD') AS [WORK_EXP_DAY], data was loaded without error but the result is not in day difference, returned as the follows as numeric calculation.
1) Why the load cannot refer to the renamed fields in script [F_FROM_DATE] & [F_TO_DATE]?
2) I also want to convert the interval in Month and Year with the two lines commented out, but guess they would return error of “Field not Found - <WORK_EXP_DAY> as well? What is the correct script for this?
3) Alternatively, how to convert the interval result in the format of 'n Year n Month n Day'?