To find number of hours between start date and end date in excel or power bi?

Question

I have a data having two date columns. SHUT HOURS column I calculate after subtracting FEEDON from FEEDOFF date. I need the information for number of shut hours on each day. If I just subtract FEEDON from FEEDOFF, it will give me total number of hours for all days. But I want for each day. Is there any way where I can find number of SHUT HOURS on each day from FEEDOFF and FEEDON date?

The output I want is in the following picture. For example, for first row I want all dates in different cells corresponding to different number of hours. Is there any way to do that?

Answer 1

This is ARRAY (CSE) formula. Press Control+Shift+Enter after pasting the formula.

Formula in G1 is

=ABS(IF(COUNTIFS($A$2:$A$4,"<="&E1+86399/86400,$B$2:$B$4,">="&E1)>0,1,0)*IFERROR(IF(MATCH(E1,INT($A$2:$A$4),0)=MATCH(E1,INT($B$2:$B$4),0),INDEX($B$2:$B$4,MATCH(E1,INT($B$2:$B$4),0),1)-INDEX($A$2:$A$4,MATCH(E1,INT($A$2:$A$4),0),1),""),IFERROR(INDEX($A$2:$A$4,MATCH(E1,INT($A$2:$A$4),0),1)-E1-1,1)*IFERROR(INDEX($B$2:$B$4,MATCH(E1,INT($B$2:$B$4),0),1)-E1,1))*24)

Pasting again being too long to scroll

=ABS(IF(COUNTIFS($A$2:$A$4,"<="&E1+86399/86400,$B$2:$B$4,">="&E1)>0,1,0)*IFERROR(IF(MATCH(E1,INT($A$2:$A$4),0)=MATCH(E1,INT($B$2:$B$4),0),INDEX($B$2:$B$4,MATCH(E1,INT($B$2:$B$4),0),1)-INDEX($A$2:$A$4,MATCH(E1,INT($A$2:$A$4),0),1),""),IFERROR(INDEX($A$2:$A$4,MATCH(E1,INT($A$2:$A$4),0),1)-E1-1,1)*IFERROR(INDEX($B$2:$B$4,MATCH(E1,INT($B$2:$B$4),0),1)-E1,1))*24)

Got help from this link

This will fail if the dates are overlapping as in case of your fourth row is overlapping the first and the third.