# Formula to show hours and minutes between times

#### jim101

##### Board Regular
I need a formula to tell me how many hours and minutes are between 10:00 pm to 8:00 am

if in cell B2 I have 10/18/11 8:17 AM and in cell C2 I have 10/19/11 10:51 AM the formula should show 10 hours

if in cell B2 I have 10/18/11 5:00 PM and in cell C2 I have 10/19/11 4:45 AM the formula should show 6 hours and 45 minutes

Can this be done using excel 2003? Thanks

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
If you format B2 and C2 as
Code:
``mm/dd/yy hh:mm``
and
Code:
``h:mm``

Code:
``=C2-B2``

I need a formula to tell me how many hours and minutes are between 10:00 pm to 8:00 am

if in cell B2 I have 10/18/11 8:17 AM and in cell C2 I have 10/19/11 10:51 AM the formula should show 10 hours

if in cell B2 I have 10/18/11 5:00 PM and in cell C2 I have 10/19/11 4:45 AM the formula should show 6 hours and 45 minutes

Can this be done using excel 2003? Thanks

Am I looking at this the wrong way. Isn't the difference between '10/18/11 8:17' (Oct18 2011 8:17AM) and '10/19/11 10:51' (Oct19 2011 10:51 AM) 26.5667 hrs???
I got this using this formula

Code:
``=(ROUND(\$C2-\$B2,0)*24+((\$C2-\$B2)-ROUND(\$C2-\$B2,0))*24)``

This will give you hrs. & mins.
Formula in D2

<TABLE style="WIDTH: 119pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=159><COLGROUP><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 119pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2903153 height=20 width=159><TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=533><COLGROUP><COL style="WIDTH: 400pt; mso-width-source: userset; mso-width-alt: 19492" width=533><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 400pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2903153 height=20 width=533>=INT((C2-B2)*24)&" hrs. "&ROUND(((((C2-B2)*24)-INT((C2-B2)*24))*60),0)&" mins."</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 358pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=477><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d9d9d9; WIDTH: 29pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=38> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 108pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2903153 class=xl66 width=144>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 102pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=136>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 119pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=159>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d9d9d9; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>row 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>10/18/11 8:17 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>10/19/11 10:51 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>26 hrs. 34 mins.</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>

I need a formula to tell me
#VALUE!
#VALUE!
#VALUE!
Excel 2003
Cell Formulas
RangeFormula
D2=IF(MOD(B2,1)TIME(8,0,0),TIME(8,0,0),MOD(C2,1))

drsarao, yes that is what I want, when I put your formula in I get the formula you typed contains an error, when I click ok the 22 in the formula is highlighted??

A slightly different simpler idea.

In cell k1 I have the time 22:00 hrs and in L1 08:00 hrs

K2 contains start time 18/10/2011 08:17
and L2 finish time 19/10/2011 10:51

N2 the formula:
=MIN(L2,INT(L2)+\$L\$1)-MAX(INT(K2)+\$K\$1,K2)

Kelbo

Kelbo, thanks that works
Jim

Somehow, HTML maker messed up:
=IF(MOD(B2,1)>TIME(22,0,0),TIME(2,0,0),TIME(24,0,0)-MOD(B2,1))+IF(MOD(C2,1)>TIME(8,0,0),TIME(8,0,0),MOD(C2,1))
However, Kelbo's formula is more elegant!

Its not the HTML maker but the forum software. See the second point in my signature. It doesn't distinguish between Excel and html. I guess everyone learns it the HARD way!

Replies
1
Views
522
Replies
10
Views
248
Replies
6
Views
184
Replies
10
Views
241
Replies
12
Views
267

1,202,909
Messages
6,052,493
Members
444,587
Latest member
ezza59

### 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.

### Which adblocker are you using?

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

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