Difference between Dates which are in String Format

[dsahu]

I want to create a column which will store the difference between 2 dates which are in string format. Knowing dateDiff function would work, however, my dates are in string format and dateDiff wont take string as arguments.(screenshot 1) Example-

D1= 20230716
D2= 20230715

Expected Resultant Column Diff= 1 (which is D1-D2).

Note- We simple want to know, how many days the file arrived late than the expected date.

And I would like to see the result as a difference of the below 2 strings. Is there any approach, as dateDiff/difference function is not working because of the following error(screenshot 2) .

Could anyone please provide a calculation to find the difference.

[jose_burrull]

HI dsahu:

For your request, you must convert to date format both fields, with 3 functions: parseDate, concat and substring. Then, use function dateDiff to calculate the difference between both dates.

Here is the script:
dateDiff(
parseDate(concat( substring(fileparsedate,1,4),‘/’,substring(fileparsedate,5,2),‘/’, substring(fileparsedate,7,2) ),‘yyyy/MM/dd’),
parseDate(concat( substring({File_Arrival_Time_ParseDdate},1,4),‘/’,substring({File_Arrival_Time_ParseDdate},5,2),‘/’,substring({File_Arrival_Time_ParseDdate},7,2) ),‘yyyy/MM/dd’) ,“DD”
)

Another way, same result:

calculated field date_fileparsedate = concat( substring(fileparsedate,1,4),‘/’,substring(fileparsedate,5,2),‘/’,substring(fileparsedate,7,2) )
calculated field date_File_Arrival_Time_ParseDdate = concat( substring({File_Arrival_Time_ParseDdate},1,4),‘/’,substring({File_Arrival_Time_ParseDdate},5,2),‘/’,
substring({File_Arrival_Time_ParseDdate},7,2) )
calculated field date2 = parseDate({date_fileparsedate},‘yyyy/MM/dd’)
calculated field date1= parseDate({date_File_Arrival_Time_ParseDdate},‘yyyy/MM/dd’)
calculated field date1-date2= dateDiff(date1,date2,“DD”)

This result is in number of days between dates, you can change formula to “MM” and this returns the month portion of the difference between both dates, etc.

Regards, José Burrull

2023-10-03T03:00:00Z

[dsahu]

@jose_burrull Thank you so much for the response. I tried the first option and it is working well for my reports . Appreciating your help in this. Thank you so much!!