Forum Discussion
Solved
Difference hours between two columns one is first row and next is in another row
| Start Date | End date | downtime |
| 08-06-201408:55:35 | 08-06-201409:55:35 | |
| 08-06-201413:55:35 | 08-06-201413:55:35 | 4 |
I want to calculate downtime in hrs based on the end date of first row and start date of the second row, also at the start of everyday downtime value should be empty
Hi ganapathya,
You can try to use below formula:
down time = DATEDIFF(MAXX(FILTER(ALL(Sheet2),[ID]=EARLIER(Sheet2[ID])&&[End date]<EARLIER(Sheet2[Start Date])),[End date]),[Start Date],HOUR)+0
Regards,
Xiaoxin Sheng
6 Replies
Hi ganapathya,
I'd like to suggest you use power query to add a index column, then use this index column to calculate time diff.
Calculate column:
down time = DATEDIFF(MAXX(FILTER(ALL(Sheet6),[End date]<EARLIER(Sheet6[Start Date])),[End date]),[Start Date],HOUR)+0
Regards,
Xiaoxin Sheng
ID Start Date End date downtime Machine 1 08-06-201408:55:35 08-06-201409:55:35 4 Machine 1 08-06-201413:55:35 08-06-201415:55:35 1 Machine 1 08-06-201416:55:35 08-06-201417:55:35 2 Machine 1 08-06-201419:55:35 08-06-201423:55:35 0 Machine 2 09-06-201400:30:00 09-06-201401:30:00 2 Machine 2 09-06-201403:30:00 09-06-201405:30:00 2 Machine 2 09-06-201407:30:00 09-06-201423:59:59 0 For this above table ,same Query will work or some change is required please help me
No sir, it is not working downtime has showing different data, please check the below data
provide the changes accordingly
Chamber Starttime endtime downtime CH1 4/25/2016 9:00:11 pm 4/26/2016 7:58:53 am 0 CH1 4/26/2016 8:28:21 Am 4/26/2016 5:44:19 Pm endtime of row 1-starttime of row 2 CH1 4/26/2016 6:37:08 Am 4/27/2016 3:25:36 am endtime of row 2 -starttime of row3 CH1 4/27/2016 4:03:24 am 4/27/2016 12:57:32 am endtime of row 3-starttime of row 4 CH1 4/27/2016 5:29:00 pm 4/28/2016 2:44:38 am endtime of row 4 -starttime of row5 ch1 4/28/2016 12:09:09pm 4/28/2016 8:36:55pm endtime of row 5 -starttime of row 6
