Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two fields : 1) date and time received : 12/16/2013 07:23 am
2) date and time sent : 12/17/2013 05:00 pm
No how can i find the total hours between date and time sent and date and time received.
Thanks & Regards
Jyothish KC
Hi,
Try this script
LOAD *,
IntervaL(Timestamp#([Date sent] & ' '& [Time Sent], 'D-MMM-YY HH:mm TT') - Timestamp#([Date Received] & ' '& [Time Received], 'D-MMM-YY HH:mm TT'), 'H') AS Diff
INLINE [
Name,Date Received, Time Received, Date sent, Time Sent
a, 17-Dec-13, 3:44 AM, 18-Dec-13, 2:39 PM
b, 17-Dec-13, 3:44 AM, 17-Dec-13, 3:25 PM
c, 17-Dec-13, 3:44 AM, 23-Dec-13, 3:25 PM];
Regards,
Jagan.
Hi,
Try using Interval()
=Interval(TimeSent - TimeReceived, 'H')
Example:
=IntervaL(Timestamp#('12/17/2013 05:00 pm', 'MM/DD/YYYY HH:mm TT') - Timestamp#('12/16/2013 07:23 AM', 'MM/DD/YYYY HH:mm TT'), 'H')
Output : 33
Regards,
Jagan.
Thanks for the reply, but still i am getting a difference of 23:00 for intervals more than 1 day.
Eg: 12/15/2013 01:00 PM ( date received)
12/17/2013 4:00 PM ( date Sent)
Hi jyothish,
Its returning 51hr, By using Jagan's logic.
- Regards,
Vishal Waghole.
Hi Jyothish,
Try this script,
Diff:
LOAD *Inline [
Received ,Sent
12-16-2013 07:23,12-17-2013 05:00
];
Diff01:
LOAD Timestamp#(Received,'MM-DD-YYYY HH:mm') as Received_Conv, Timestamp#(Sent,'MM-DD-YYYY HH:mm') as Sent_Conv Resident Diff;
Diff02:
LOAD Interval(Sent_Conv-Received_Conv,'HH') as Diffrence Resident Diff01;
Hi,
You can try hour(time((date sent),'hh:mm:ss'))-(time(date recieved),'hh:mm:ss').
Regards,
Piyush
Hi,
Check this, it is working and it returns 51 as output
=IntervaL(Timestamp#('12/17/2013 4:00 PM', 'MM/DD/YYYY HH:mm TT') - Timestamp#('12/15/2013 01:00 PM', 'MM/DD/YYYY HH:mm TT'), 'H')
Regards,
Jagan.
Hi Jagan,
Actually i have two column " Date" and other is " Received Time". So i am combing both fields in Excel and creating "Date and time received" field.Is there any way to create this field in QV instead of excel. I think because of this i am facing issue.
Hi,
Can you attach some raw sample data in Excel and come up with an example what actually you need?
Regards,
Jagan.
In My Case Elapsed Time is not coming beyond 23 .