9 Replies Latest reply: May 27, 2014 5:23 AM by shweta singh

# How to calculate the time  difference from the same column values .

Hi ,

I want to calculate the Time difference of the values from the AU_TIME.

Then check the status for how much time the status was there .

I have attached the excel file also please check .

Thanks .

• ###### Re: How to calculate the time  difference from the same column values .

These two timestamps 1/28/2014  9:53:51 PM, 2014-01-28 21:53:51 are same values in different format. Can you explain how you calculate Closed status time?

• ###### Re: How to calculate the time  difference from the same column values .

with what should we compare the AU_TIME?

• ###### Re: How to calculate the time  difference from the same column values .

Each Product line have different AU_Time.

Like in column:

Product Line     ,AU_Time ,        Status

 Eclipse 2014-01-28 21:53:51 Closed Eclipse 2014-01-28 21:53:51 ReTest Eclipse 2014-01-28 21:53:51 Closed Eclipse 2014-01-28 22:28:28 Closed

I need calculate first deffrnc with AU_Time foreach row.

(2014-01-28 21:53:51 - 2014-01- 28 21:53:51) in hours =12hrs. .

then will say that Eclipse was in closed status for 12 hours.

then next we will calculate (2014-01-28 21:53:51 - 2014-01-28 21:53:51)in hrs =0hrs so the status will be retest .

Eclipse remain in retest status for 0 hrs.

• ###### Re: How to calculate the time  difference from the same column values .

Status is given already in the table no need to calculate it  these three columns are provided already.you need to calculate like:

Each Product line have different AU_Time.

Like in column:

Product Line     ,AU_Time ,        Status

 Eclipse 2014-01-28 21:53:51 Closed Eclipse 2014-01-28 21:53:51 ReTest Eclipse 2014-01-28 21:53:51 Closed Eclipse 2014-01-28 22:28:28 Closed

I need calculate first deffrnc with AU_Time foreach row.

(2014-01-28 21:53:51 - 2014-01- 28 21:53:51) in hours =12hrs. .

then will say that Eclipse was in closed status for 12 hours.

then next we will calculate (2014-01-28 21:53:51 - 2014-01-28 21:53:51)in hrs =0hrs so the status will be retest .

Eclipse remain in retest status for 0 hrs.

• ###### Re: How to calculate the time  difference from the same column values .

HI Try this,

First of all fetch hour from given time stamp in back end script using:

example

 Main:

AU_TIME,

Hour(AU_TIME) as hour,

Status

FROM

[C:\Documents and Settings\CNLT2011\Desktop\status of Product.xlsx]

(ooxml, embedded labels, table is Sheet1);

=hour-Above(hour)

• ###### Re: How to calculate the time  difference from the same column values .

Thanks Shweta ,

I am able to get the difference in time now.But how to calculate the status time .for eg

Product Line     ,AU_Time ,   Differnce     Status

 Eclipse 22          ,   0 Closed Eclipse 32   ,     10 ReTest

The Product Line Eclipse was in Status Closed For 10 hours .

Same steps for other entries also

• ###### Re: How to calculate the time  difference from the same column values .

tell me your logic  i mean on which basis  you want to calculate status

• ###### Re: How to calculate the time  difference from the same column values .

Status is already in the column i just need to calculate the time taken by each status.

The difference is the difference between AU_Time  . then we will check that Status was closed for 10 hours .then it changed to Retest