Count minutes between two time/date fields

S

safetymast

Does anyone have a formula they can give me to count the minutes between the
two columns below?

Dec.19.2007 21:36:23 Dec.19.2007 21:41:07
 
J

JE McGimpsey

Dates and times in XL are just floating point number offsets from a base
date, with the integer part representing days, and the fractional part
"partial days". There are 1440 minutes in a day, so

C1: =ROUND((B1-A1)*1440, 0)

will give you the number of minutes.
 
R

Ron Rosenfeld

Does anyone have a formula they can give me to count the minutes between the
two columns below?

Dec.19.2007 21:36:23 Dec.19.2007 21:41:07
Click to expand...


=SUBSTITUTE(SUBSTITUTE(B1,"."," ",1),".",", ")-
SUBSTITUTE(SUBSTITUTE(A1,"."," ",1),".",", ")

Custom Format the cell as [m] or [m]:ss for minutes and seconds.

But you might want to put the SUBSTITUTE functions separately so you can have
separate cells with the values as Excel Date/time values.
--ron
 
S

safetymast

the problem i have is that the Date comes across in the same cell as the time
- how do i deal with that? thanks.
 
B

BoniM

If your cells are formated with a date/time format:
=MINUTE(B2)-MINUTE(A2)

If they are formatted as text (and the dates are always the same length):
=MID(B2,16,2)-MID(A2,16,2)

These formulas assume a start date/time in A2 and an end date/time in B2,
adjust as needed.
 
R

Ron Rosenfeld

Does anyone have a formula they can give me to count the minutes between the
two columns below?

Dec.19.2007 21:36:23 Dec.19.2007 21:41:07
Click to expand...


=SUBSTITUTE(SUBSTITUTE(B1,"."," ",1),".",", ")-
SUBSTITUTE(SUBSTITUTE(A1,"."," ",1),".",", ")

Custom Format the cell as [m] or [m]:ss for minutes and seconds.

But you might want to put the SUBSTITUTE functions separately so you can have
separate cells with the values as Excel Date/time values.
--ron
Click to expand...


The above assumes your date/times are not formatted as dates, but are text
strings.

If they have already been custom formatted as dates, then merely =B1-A1 will do
the job, and custom format the result as above.
--ron
 
R

rdwj

As McGimpsey wrote, date is just a floating number from the base date
(1/1/1900).
Thus, if you use McGimpsey's formula and format it to number you will get
the number of minutes.

Alternative, just do B1-A1 and format it in Minutes (mm) which will only
work as long as the differnce is smaller then one hour.....

rdwj
 
Top