Help in date and time.

[vinod22kv]

Hi,

I have two fieldes given bellow. B is e mail received on and A Is email responded i want to calculate the average time how much it is taking to reply for mail. for this i need A-B/count.

Now i need help how to do A-B,

A	B
5/21/2018 9:30	Sun, 20 May 2018 23:35:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:37:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:38:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:39:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:40:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:41:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:42:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:43:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:44:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:45:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:46:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:47:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:51:20 +0100

Thanks and Regards,

Vinod.

[Frank_Hartmann · Accepted solution]

Load     *,
        RowNo() as Row, 
        Date(Date#(left(right(B,26),20),'DD MMM YYYY hh:mm:ss'),'M/DD/YYYY hh:mm:ss') as AOld,
        Date(ConvertToLocalTime(Date(Date#(left(right(B,26),20),'DD MMM YYYY hh:mm:ss'),'M/DD/YYYY hh:mm:ss'), 'GMT+01:00'),'M/DD/YYYY hh:mm:ss') as BNew,        
        Date(Date#(A,'M/DD/YYYY hh:mm'),'M/DD/YYYY hh:mm:ss') as ANew;
LOAD * INLINE [

    A, B
    5/21/2018 9:30, "Sun, 20 May 2018 23:35:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:37:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:38:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:39:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:40:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:41:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:42:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:43:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:44:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:45:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:46:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:47:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:51:20 +0100"
];

[vinod22kv]

It is working fine with inline load.

But it is not working when we have loded the excel.

[Frank_Hartmann]

whats your date format in Excel?

Can you Upload your Excel file? You can erase all the columns except the two  date columns...

[vinod22kv]

Hi,

It is working fine the format in the excel was different.

Thanks a lot for your help..

[vinod22kv]

Hi,

Can you please extract hours form the same data and share me the script.

i am using bellow function

date(date#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS Hour,

     Timestamp(Timestamp#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS hor,

but i am not able to get the hour for all the records.

Please help me out.

[Frank_Hartmann]

Date(Date#(left(right([Date Time],26),20),'DD MMM YYYY hh:mm:ss'),'hh') as AHour,
Date(ConvertToLocalTime(Date(Date#(leftDate(ConvertToLocalTime(Date(Date#(left(right(B,26),20),'DD MMM YYYY hh:mm:ss'),'M/DD/YYYY hh:mm:ss'), 'GMT+01:00'),'hh') as BHour,