How do I calculate difference in minutes in MS Excel 1

[aruba]

How would I calculate the difference in minutes between the following dates, ignoring the difference in days in MS Excel:
7-21-04 1:10 AM
7-14-04 12:10 AM
I'm trying to get a result of 1 hr.
Thanks!

 

[cLFlaVA]

You can just subtract the date/time values in another cell, and format that cell as Time, with the Type set to ##:##.

In the cell, set the formula as =A1 - A2
Select the new cell and click Format > Cells... in the menu bar
On the Number tab, indicate Category as Time and type as ##:##

This should display the time difference only.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[st24v6]

Simple answer is:

Cell containing 7-21-04 1:10 AM
Minus
Cell containing 7-14-04 12:10 AM
Then format the equation cell to
Custom: [h]:mm:ss
This should return a result of 157:00:00 (or it did for me)

PJ

"If you don't know how, ask 1st"

 

[Zathras]

Somehow I don't think 157 hours is an acceptable number.

Try this formula:

=ABS((A1-TRUNC(A1))-(A2-TRUNC(A2)))*60*24

It first strips off the day portion of the date and then converts the fractional day to minutes (60 minutes in an hour, 24 hours in a day).

 

[st24v6]

157 is an acceptable number to me if I was looking to have the reult in hours and not days as the original request was phrased, or am I being picky?

PJ

"If you don't know how, ask 1st"

 

[cLFlaVA]

I'm trying to get a result of 1 hr.

I think the approach here is to just get a difference in hours of the day, not necessarily total hours. You can see by the example that aruba wants an answer of 1 hour, even though these two date/times are ~7 days different.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[xlbo]

The requirement is to only show the difference (in minutes) between the 2 TIMEs ignoring the DATES ??

In which case

If end date & time in B1 and start date & time in A1

=(B1-Int(B1))-(A1-Int(A1))

is the formula you need - format as mm or hh:mm:ss - whatever you want

the B1-Int(B1) takes the date and time and reduces it to time only as dates & times are merely numbhers with 1 = 1 day. The time is therefore the DECIMAL part of the number - to get at that, you just subtract the integer (INT) of the number from the whole number...et voila

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[st24v6]

Is it Friday yet?
Everytime I try to help......mumble, mumble, mumble,
think I'll just stick to asking question in future and not try to help anyone but myself!
Never mind can't read everything right

PJ

"If you don't know how, ask 1st"

 

[aruba]

Thanks so much to all of you. I ended up using Zathras solution which solved my problem.
Thanks again for all your help!