How to use dates to calculate metrics

[#1]

I have the need to use a created date and an resolved date to calculate the amount of time in between (Hopefully into days and seconds), additionally I would need to exclude weekends.  Can someone help me get started with this?

Number	Created	Resolved
1234	8/13/2015 9:00:00 AM	8/18/2015 3:00:05 PM
1235	8/14/2015 9:45:04 AM	8/19/2015 2:01:04 PM
1236	8/22/2015 4:05:12 PM	8/24/2015 5:30:05 PM

[Anonymous · Accepted solution]

hi,

please try:

Re: Calculate Elapsed Time Nett

Calculate hours between two Date/Time strings

Regards

Neetha

[#3]

Thank you, that helped a lot.

The only issue I now have is that the duration field is showing in a time format, which is OK but it shows, AM or PM when in reality it is really just a count of hours:minutes:seconds.  Is there a way to change this in the data set?

Set TimestampFormat = 'M/D/YY hh:mm TT';

Input:

LOAD Number,

     Created,

     Resolved

FROM

(ooxml, embedded labels, table is [Crp to 20.08.15]);

Temp:

LOAD  Number,

daystart(Created)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(Created),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(Created)+iterno()-1=daystart(Resolved), rangemax(maketime(8),rangemin(frac(Resolved),maketime(18))),Maketime(18)) as End

Resident Input

while daystart(Resolved) >= daystart(Created)+iterno()-1;

left join (Input)

LOAD

Number,

interval(sum(End-Start)) as Duration

Resident Temp  where WeekDay(Date)<5  group by Number;

drop table Temp;

[Anonymous]

Hi,

Try:

interval(sum(End-Start),'hh:mm:ss')

or

=dual(

floor(Interval) &' days ' & hour(Interval) & ' h ' & minute(Interval) & ' min ' & second(Interval) & ' sec'

, Interval

)