Time distribution within a 24 hour period

[HowToAsk]

I need some help in distributing time.
Suppose you div a 24 hour period into 3 parts.
07 - 17
17 - 21
21 - 07

and you have a start time and a stop time
eksample: 06 - 22

How many hours of each period did you spend/visit?

Excel example:

<table border="0" cellpadding="0" cellspacing="0" width="756"><col style="width: 104pt;" width="138"> <col style="width: 137pt;" width="183"> <col style="width: 113pt;" width="150"> <col style="width: 154pt;" width="205"> <col style="width: 60pt;" width="80"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 104pt;" height="20" width="138">
</td> <td style="width: 137pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="183">Period start</td> <td style="width: 113pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="150">Period stop</td> <td style="width: 154pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 1.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="205">Period number</td> <td style="width: 60pt;" width="80">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">21:00</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">07:00</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">07:00</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">17:00</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">2</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color white -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">17:00</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color white -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">21:00</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">3</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Time of start</td> <td>Time of stop</td> <td>Within the SAME 24 hour period</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl65" align="right">07:00</td> <td class="xl65" align="right">18:00</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl66">Hours in P1</td> <td class="xl66">Hours in P2</td> <td class="xl66">Hours in P3</td> <td class="xl66">Total</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Manually</td> <td class="xl66">0</td> <td class="xl66">10</td> <td class="xl66">1</td> <td class="xl66">11</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Dynamicly</td> <td class="xl66">x</td> <td class="xl66">x</td> <td class="xl66">x</td> <td class="xl66">x</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td colspan="2" style="height: 15pt;" height="20">Should also work for nights, </td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Start time</td> <td class="xl65" align="right">07:00</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Stop time</td> <td class="xl65" align="right">06:00</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td colspan="3" style="height: 15pt;" height="20">The case with start = stop time, this is considered a 24 hour period</td> <td>
</td> <td>
</td> </tr> </tbody></table>

Thanks in advance

MS excel 2007 on win7

 

[Toonies]

Hi this may help I got it somewhere but not quite sure

Excel Workbook
	A	B	C	D	E	F	G	H
1			0:00	7:00	17:00	21:00	31:00	41:00
2			7:00	17:00	21:00	31:00	41:00	48:00
3
4	21:00	12:00	0	0	0	10	5	0
Sheet

 

[HowToAsk]

I does not work for me.
I will try a bit more, thank you for your help.


Any other ideas are also welcome

 

[Toonies]

Hi try this download link to the file

http://www.mediafire.com/?15t8ajftqkulm03

 

[HowToAsk]

Thank you very much, it works great!