Count total minutes between 2 times

[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

 

[Fred Smith]

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

Format as a number

Regards,
Fred

 

[Per Jessen]

Hi

=Cell2-Cell1

Format formula cell as custom: [mm]

Hopes this helps.
....
Per

 

[cpliu]

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.

 

[Dave Peterson]

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.

 

[Dave Peterson]

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.

 

[dflak]

If the cells are strings would no
1400*((DateValue(Cell1)+TimeValue(Cell1))-(DateValue(Cell2)+TimeValue(Cell2))

Aslo work?