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
| 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)....
Hi ganapathya,
Please double check your sample data if it contains the wrong records:
After I modify the highlight part, the formula works well.
Regards,
Xiaoxin Sheng