Comparing different times (in hours, minutes, seconds) over days

hahbida

New Member
Joined
Mar 3, 2008
Messages
2
I have a Created Time and and Ending Time which may well be different days (example below, created in green; ending in red).<TABLE style="WIDTH: 301pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=400 border=0 x:str><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=129 height=17 x:num="39491.589004629626">2/13/08 2:08 PM</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 100pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=133></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=138 x:num="39497.452245370368">2/19/08 10:51 AM</TD></TR></TBODY></TABLE>

I am looking to create the number of elapsed hours and minutes only in the results-- I can and have formatted the number of days, hours, minutes, seconds (as d:hh:mm:ss), but want the results to be ONLY in hours and minutes.

Any help anyone can provide would be much appreciated-- I have formatted the cells as dates (both in AM/PM and 24 hour clock format), with no luck so far...

Thanks!
Chris
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try this:
=(DAY(B1-A1)*24)+HOUR(B1-A1)&" Hours : "&MINUTE(B1-A1)&"Mins"
 
Upvote 0
Perfect! Worked great, and I love the format of the output (puts it into easily tranlated 'human' terms).
Last thing-- IF I get a #NUM returned (the result of not having a creation date or an ending date in one of the columns), can I account for this as part of the formula you provided?

* I can't thank you enough-- you just saved me hours of trying to figure this out myself, and google'ing didn't yield a palatable response...but you did! cw
 
Upvote 0
Ok, give this one a whirl:

=IF(ISERROR(DAY(B1-A1)*24),"",(DAY(B1-A1)*24)+HOUR(B1-A1)&" Hours : "&MINUTE(B1-A1)&" Mins")
 
Upvote 0
=(DAY(B1-A1)*24)+HOUR(B1-A1)&" Hours : "&MINUTE(B1-A1)&"Mins"

Note: this only works correctly for up to 768 hours (32 days) any time period 32 days or greater will give an incorrect result. You could try

=TEXT(B1-A1,"[h] ""hours"" mm ""mins""")

which returns a text string...or for easier use in calculations just use

=B1-A1

and custom format result cell as

[h] "hours" mm "mins"

edit: to show result only if both cells contain values

=IF(COUNT(A1:B1)=2,=TEXT(B1-A1,"[h] ""hours"" mm ""mins"""),"")
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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