14 Replies Latest reply on Jul 8, 2018 11:09 PM by Ritesh Bisht

# Calculate Datediff between one date column

I need to calculate datediff with one date column with structure as

 Time stamp Battery voltage Condition 01/04/2016 00:00 51 Yes 01/04/2016 00:01 52 Yes 01/04/2016 00:02 45 No 01/04/2016 00:03 48 No 01/04/2016 00:04 49 No 01/04/2016 00:05 55 Yes 01/04/2016 00:06 54 Yes 01/04/2016 00:07 52 Yes 01/04/2016 00:08 51 Yes 01/04/2016 00:09 49 No 01/04/2016 00:09 48 No 01/04/2016 00:11 47 No 01/04/2016 00:12 45 No 01/04/2016 00:12 50 Yes 01/04/2016 00:13 51 Yes

I have a condition here, in third column which is as follows,

If Battery voltage >50 , "No"

If battery voltage >51, "Yes"

So I need to calculate total number of mins a machine is running in battery.

How to calculate datediff based on this.

Any help will be really appreciated.

Thanks

• ###### 1. Re: Calculate Datediff between one date column

Hi,

Need some explanation on total running time. Did you mean the battery is running when condition= "Yes" and you want to calculate the total duration for which the battery is running.

Thanks,

Rahul

• ###### 2. Re: Calculate Datediff between one date column

Hi Krishnamoorthy,

Based on my assumptions that you want to calculate the time duration for which condition = Yes, i have created a calculated field for difference in time stamp

Time Stamp Difference>

IF FIRST()=0 THEN 0

else DATEDIFF('minute',LOOKUP(MIN([Time stamp]),-1),LOOKUP(MIN([Time stamp]),0))

END

After that just take the sum where condition is Yes.

Attached file for reference

Regards,

Rahul

• ###### 3. Re: Calculate Datediff between one date column

Absolutely Rahul Singh,

If Condition== "Yes" I need to calculate the total duration for which the battery is running based on Time.stamp(which has hours)

• ###### 4. Re: Calculate Datediff between one date column

Ohh..then i calculated the same thing. Check the solution i shared. Hope it helps you.

• ###### 5. Re: Calculate Datediff between one date column

Hi Krishnamoorthy,

Below will work if you have battery report every min. (we are fine either status, Yes or No, but it should be there  , I can see your report is based upon every min )

As per the report we should get 4 mins overall  (12:00 - 12:01 ) &  ( 12:05-12:08)  1+3=4

Using below can break the calculation since if you use it you will get 4 when coming down from 12:01 to 12:05 , hence we need to ignore it. (remember condition is No from 12:01 to 12:05)

DATEDIFF('minute',LOOKUP(MIN([Time stamp]),-1),LOOKUP(MIN([Time stamp]),0))

I have added a separate calculation which will avoid such scenario.

Are you looking for the same , then please find the attached.

I can explain further but want to know if you are looking for the same

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 6. Re: Calculate Datediff between one date column

Hi Krishnamoorthy,

Did it help ?

Thanks,

Ritesh

• ###### 7. Re: Calculate Datediff between one date column

Hi Krishnamoorthy,

Did it help ?

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 8. Re: Calculate Datediff between one date column

Hi Ritesh,

It worked, sorry for late response. Thanks.

• ###### 9. Re: Calculate Datediff between one date column

no worries, appreciate if you can set it as CORRECT as well in order to close it.

Thanks,

Ritesh

• ###### 10. Re: Calculate Datediff between one date column

I am glad that it helped you

Kindly hit the Correct Answer button as below to close it as well

Regards,

Ritesh

• ###### 11. Re: Calculate Datediff between one date column

It is a nice practise to keep our forum neat and clean by closing the thread.

I am glad that it helped you

Kindly hit the Correct Answer button to close it

Regards,

Ritesh

• ###### 12. Re: Calculate Datediff between one date column

It is a nice practise to keep our forum neat and clean by closing the thread.

I am glad that it helped you

Kindly hit the Correct Answer button to close it

Thanks,

Ritesh

• ###### 13. Re: Calculate Datediff between one date column

Hi Ritesh,

Since I am new to this community I couldn't find correct answer icon anywhere.

• ###### 14. Re: Calculate Datediff between one date column

Hi Dear Krishnamoorthy ,

It is a recommended practise to mark the answers accordingly in order to keep our forum clean.

Please mark any of the answer as CORRECT/HELPFUL if it really helped you so that it can help others as well.

I think I got your issue, you need to come out of INBOX and go to original post.

Click on below and set it as Correct.

Calculate Datediff between one date column

Thanks,

Ritesh