Total Number of Minutes

[reyrey]

Im trying to figure out a excel function that would capture the "total number of minutes" based of the following criteria.


[table="width: 1000, class: grid"]
[tr]
[td]Start Day
[/td]
[td]End Day
[/td]
[td]Start Date Time
[/td]
[td]End Date Time
[/td]
[td]Total number of minutes
[/td]
[/tr]
[tr]
[td]10/4/2017 0:00
[/td]
[td]10/4/2017 23:59
[/td]
[td]10/4/2017 22:00
[/td]
[td]10/4/2017 23:00
[/td]
[td]90[/td]
[/tr]
[tr]
[td]10/5/2017 0:00
[/td]
[td]10/5/2017 23:59
[/td]
[td]10/5/2017 23:30
[/td]
[td]10/6/2017 2:30
[/td]
[td]810[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td]10/4/2017 23:30
[/td]
[td]10/5/2017 13:00
[/td]
[td][/td]
[/tr]
[/table]

 

[Jonmo1]

How are you getting 90 and 810 minutes for those?
Surely 22:00 to 23:00 is 60 minutes (1 hour)
and 23:30 to 2:30 is 180 minutes (3 hours)

Try
=(D2-C2)*1440

 

[reyrey]

90 minutes was from: cell C2 and D2 (60 minutes) plus C3 and D3 (30 minutes)
810 minutes was from: cell C3 and D3 (30 minutes) plus C3 and D3 (780 minutes)

Trying to get the total number of minutes between C2 and D2 based on the time range between A2 and B2
So for example: between Start Day and End Day, I want to see how many minutes were complete from Start Date Time and End Date Time.

 

[barry houdini]

You can use this array formula in E2

=1440*SUM(IF(C$2:C$4 >B2,0,IF(D$2:D$4<A2,0,IF(D$2:D$4 >B2,B2,D$2:D$4)-IF(C$2:C$4< A2,A2,C$2:C$4))))
<a2,0,if(d$2:d$4><a2,a2,c$2:c$4))))
<a2,0,if(d$2:d$4><a2,a2,c$2:c$4))))

confirmed with CTRL+SHIFT+ENTER and copied down

You won't get exactly the results you suggest because the "End Day" periods end at a minute to midnight - if you want to get the results 90 and 910 then change B2 to 10/5/2017 00:00 and B3 to 10/6/2017 00:00</a2,a2,c$2:c$4))))
</a2,0,if(d$2:d$4></a2,a2,c$2:c$4))))
</a2,0,if(d$2:d$4>