Formula to calculate/split out hours in range

MrMaker

New Member
Joined
Jun 7, 2018
Messages
40
Morning,

I need a little bit of help please.

Simply, I have a start date/time and an end time/date, and I need to split out the total number of hours (or decimal days) in the range so that they are apportioned to each day that they fall in:

Data:

A2) arrived date/time 01/02/2018 09:00

B2) end date/time 03/02/2018 15:30

Output Requirements:

C2 ) how many hours in the above were on 01/02/18 (i.e. between 01/02/2018 09:00 and 01/02/2018 23:59)

D2) how many hours in the above were on 02/02/18 (i.e. between 02/02/2018 00:00 and 02/02/2018 23:59)

E2) how many hours in the above were on 03/02/18 (i.e. between 03/02/2018 00:00 and 03/02/2018 15:30)

Hopefully this makes sense and thank you in advance
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Try:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">2/1/2018 9:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">2/3/2018 18:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">start</td><td style="text-align: right;;">2/1/2018 9:00</td><td style="text-align: right;;">2/2/2018 0:00</td><td style="text-align: right;;">2/3/2018 0:00</td><td style="text-align: right;;">2/3/2018 15:30</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">end</td><td style="text-align: right;;">2/2/2018 0:00</td><td style="text-align: right;;">2/3/2018 0:00</td><td style="text-align: right;;">2/3/2018 15:30</td><td style="text-align: right;;">2/3/2018 23:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;"># of hours</td><td style="font-weight: bold;text-align: right;background-color: #D0E7F7;;">15.0</td><td style="font-weight: bold;text-align: right;background-color: #D0E7F7;;">24.0</td><td style="font-weight: bold;text-align: right;background-color: #D0E7F7;;">15.5</td><td style="font-weight: bold;text-align: right;background-color: #D0E7F7;;">2.5</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D7</th><td style="text-align:left">=MAX(<font color="Blue">0,MIN(<font color="Red">D6,$B$3</font>)-MAX(<font color="Red">D5,$B$2</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E7</th><td style="text-align:left">=MAX(<font color="Blue">0,MIN(<font color="Red">E6,$B$3</font>)-MAX(<font color="Red">E5,$B$2</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F7</th><td style="text-align:left">=MAX(<font color="Blue">0,MIN(<font color="Red">F6,$B$3</font>)-MAX(<font color="Red">F5,$B$2</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G7</th><td style="text-align:left">=MAX(<font color="Blue">0,MIN(<font color="Red">G6,$B$3</font>)-MAX(<font color="Red">G5,$B$2</font>)</font>)*24</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,136,341
Messages
5,675,203
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top