Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 2 date fields as DATE 1 AND DATE2 in the format 'DD-MM-YYYY hh:mm:ss'. i need to find date difference between these 2 fields as " min(Date1 ) - (DATE2)" and store this date diff as a separate field in table layout
i should get data as follows : I ONLY need to find the difference between first occurence of DATE1 with the corresponding DATE2
DATE1 DATE 2 DATEDIFF
17-10-2018 12:15:00 17-10-2018 12:17:00 00:02:00
17-10-2018 12:19:00 17-10-2018 12:21:00
18-10-2018 13:20:00 18-10-2018 13:25:00 00:05:00
18-10-2018 13:27:00 18-10-2018 13:31:00
..... AND SO ON...
PLS HELP ME OUT......
Thanks
date(date#(DATE1,'DD-MM-YYYY hh:mm:ss')-date#(DATE2,'DD-MM-YYYY hh:mm:ss'),'hh:mm:ss')
Choose Duration in number formatting.
Try sthing as follow:
Let's assume this is ur main table:
:
Table:
Load Date1,Date2, other fields ...;
Create a new date field based on Date1
Table:
Load Date1,Date2, other fields, Date(Date1) as MyDate ...;
Create a new aggregated table (group by MyDate):
load minDate2-minDate1 as Difference, MyDate
load min(Date1) as MinDate1, min(Date2) as minDate2, MyDate resident Table group by MyDate;
date(min(date#(DATE1,'DD-MM-YYYY hh:mm:ss'))-min(date#(DATE2,'DD-MM-YYYY hh:mm:ss')),'hh:mm:ss')
Choose Duration in number formatting.