Count total minutes between 2 times

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

Cell 1: Feb 23 2010 4:11PM
Cell 2: Mar 1 2010 4:03PM

How do I count the total minutes between 2 cells without manually
calculating?

Thanks
 
If cell 1 is A1, and cell 2 is A2, use:
=(a2-a1)*24*60

Format as a number

Regards,
Fred
 
Thank you for the quick response. Sorry I wasn't clear. The data
includes both date and time information, it spans over 1 day, and the
format isn't standard that I may need to extract the data out to
another cell, If I try cell 2 - cell 1, I got #VALUE! error. It does
not seem to know the format there.

Thanks for the help again.
 
If that's what's really in your cells, then the values aren't really
date/times. They're just plain old text that look that way to you (but not
excel).

Depending on your local settings and those abbreviated month strings, you could
convert the values to real date/times by using a helper formula like:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1)," ",", ",2),"PM"," PM"),"AM"," AM")

This worked fine with my USA settings.

You should see something like:
40232.6743055556
(with 1904 base date)

If you give that a nice custom number format:
mmm dd, yyyy hh:mm:ss
it should look like:
Feb 23, 2010 16:11:00

Then you can use those helper cells to do your date/time arithmetic.
 
Ps:

You could do the calculation in a single cell, too:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",", ",2),"PM"," PM"),"AM"," AM")
-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1)," ",", ",2),"PM"," PM"),"AM"," AM")

With a custom number format of [mm].

Since =substitute() returns a string, the other formula used -- (two minus
signs) to coerce the text to a real number.

In this formula, the subtraction does the same thing for both strings.
 
If the cells are strings would no
1400*((DateValue(Cell1)+TimeValue(Cell1))-(DateValue(Cell2)+TimeValue(Cell2))

Aslo work?
 
Back
Top