How to calculate business hours/minutes between two dates?

hardtoguess

New Member
Joined
Aug 8, 2018
Messages
2
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,772
Office Version
2013
Platform
Windows
This ??
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10/01/2018 19:15</td><td style="text-align: right;;">11/01/2018 9:00</td><td style="text-align: right;;">13.75</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">6/01/2018 11:30</td><td style="text-align: right;;">6/01/2018 22:00</td><td style="text-align: right;;">10.5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #242729;;">7/01/2018 15:30</td><td style="text-align: right;;">7/01/2018 20:00</td><td style="text-align: right;;">4.5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #242729;;">7/01/2018 16:00</td><td style="text-align: right;;">8/01/2018 3:30</td><td style="text-align: right;;">11.5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=(<font color="Blue">B2-A2+(<font color="Red">B2<A2</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=(<font color="Blue">B3-A3+(<font color="Red">B3<A3</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=(<font color="Blue">B4-A4+(<font color="Red">B4<A4</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=(<font color="Blue">B5-A5+(<font color="Red">B5<A5</font>)</font>)*24</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

hardtoguess

New Member
Joined
Aug 8, 2018
Messages
2
Hi Michael,

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

Output I am looking for as below


210/01/2018 19:1511/01/2018 9:0011.75
36/01/2018 11:306/01/2018 22:003
47/01/2018 15:307/01/2018 20:001
57/01/2018 16:008/01/2018 3:308.5

<thead>
</thead><tbody>
</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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,752
Messages
5,446,289
Members
405,395
Latest member
anilals

This Week's Hot Topics

Top