Forum Discussion
Difference hours between two columns one is first row and next is in another row
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
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 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
basically i want to calculate the hours
For example, i have date with time, same date ch1 start time and end time consider runs from 6 AM to 8.15 AM,then again from 10 AM to 11 pm...Now the downtime is 1.45 hrs...from 8.15 AM (End time of row 1) to 10 AM (Start Time Row 2)....