Formula to show hours and minutes between times

jim101

Board Regular
Joined
Mar 22, 2005
Messages
110
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
If you format B2 and C2 as
Code:
mm/dd/yy hh:mm
and
your formula location as
Code:
h:mm

your formula should simply be
Code:
=C2-B2
 
Upvote 0
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???:confused:
I got this using this formula

Code:
=(ROUND($C2-$B2,0)*24+(($C2-$B2)-ROUND($C2-$B2,0))*24)
 
Upvote 0
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>
 
Upvote 0
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))
 
Upvote 0
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??
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,192
Messages
6,170,647
Members
452,344
Latest member
LarryRSch

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
Back
Top