How to calculate business hours/minutes between two dates?

[hardtoguess]

I'm trying to calculate business hours between 7PM and 7AM.

For Example I have a below dataset

Start time <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">2018-01-10 19:15:00</code> and End time <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">2018-01-11 09:00:00</code>.
Start time <code style="font-style: inherit; font-variant: inherit; font-weight: inherit; margin: 0px; padding: 1px 5px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">2018-01-06 11:30:00</code> and End time <code style="font-style: inherit; font-variant: inherit; font-weight: inherit; margin: 0px; padding: 1px 5px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">2018-01-06 22:10:00</code>.
Start time <code style="color: rgb(36, 39, 41); font-style: inherit; font-variant: inherit; font-weight: inherit; margin: 0px; padding: 1px 5px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">2018-01-07 15:30:00</code> and End time <code style="color: rgb(36, 39, 41); font-style: inherit; font-variant: inherit; font-weight: inherit; margin: 0px; padding: 1px 5px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">2018-01-07 20:00:00</code>.


I expect calculation to return how many hours between 7PM and 7AM.



Thanks.

 

[Michael M]

This ??

Excel 2007
	A	B	C
2	10/01/2018 19:15	11/01/2018 9:00	13.75
3	6/01/2018 11:30	6/01/2018 22:00	10.5
4	7/01/2018 15:30	7/01/2018 20:00	4.5
5	7/01/2018 16:00	8/01/2018 3:30	11.5
Sheet1

Cell Formulas
Range	Formula
C2	=(B2-A2+(B2))*24
C3	=(B3-A3+(B3))*24
C4	=(B4-A4+(B4))*24
C5	=(B5-A5+(B5))*24

 

[hardtoguess]

Hi Michael,

Thanks for reply.
But I want to see only the Hours between 7pm and 7am.

Output I am looking for as below

<thead>
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[TH="align: center"][/TH]

</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]10/01/2018 19:15[/TD]
[TD="align: right"]11/01/2018 9:00[/TD]
[TD="align: right"]11.75[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/01/2018 11:30[/TD]
[TD="align: right"]6/01/2018 22:00[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7/01/2018 15:30[/TD]
[TD="align: right"]7/01/2018 20:00[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/01/2018 16:00[/TD]
[TD="align: right"]8/01/2018 3:30[/TD]
[TD="align: right"]8.5[/TD]

</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">ABC210/01/2018 19:1511/01/2018 9:0013.7536/01/2018 11:306/01/2018 22:0010.547/01/2018 15:307/01/2018 20:004.557/01/2018 16:008/01/2018 3:3011.5</body>Sorry for formatting!