Calculating times from day to day (Excel 2002)

Topic

New Reply

Is there a way to calculate a difference between two different times on two different days and give the result in minutes.

My client is looking to calculate time from admittance to hospital until time that a certain drug is administered. If it’s the same day, no problem, but since the date field is separate from the time field – how would we factor in the date field into a formula for something like this.

–*Rob

Reply | Quote

Replies

If the date and time fields are valid Excel date and time fields (not text masquerading as time or date),

	A	B	C	D	E
1	01/01/04	9:00 PM	01/02/04	9:30 AM	=C1+D1-A1-B1

and the difference calculation in cell E1 is Custom formatted as [m]:ss. Does this help?

Reply | Quote

You lost me there…what formula did you use to get the result of 750?

Reply | Quote

Sorry, meant to post that as a formula:

=C1+D1-A1-B1

Reply | Quote

Sorry, meant to post that as a formula:

=C1+D1-A1-B1

Reply | Quote

Excel keeps dates and times togeter. The date is the whole number part. The time is the fractional part.
I would go with John’s original formula, but multiply by 24*60 to convert the fraction to minutes:

=24*60*(C1+D1-A1-B1)

The safe way that John just posted uses the int function to make sure there is no hidden time, and uses the mod function to make sure there is no hidden date. I’ve attached a worksheet in case you are still confused. Date and times are tough at first. HTH –Sam

Reply | Quote

Sammy, the OP stated that the date and time fields were separate records, so I took that at face value. And Chipshot gets the credit for the safer approach of using INT and MOD to ensure that dates are only dates and times are only times.

Reply | Quote

Sammy, the OP stated that the date and time fields were separate records, so I took that at face value. And Chipshot gets the credit for the safer approach of using INT and MOD to ensure that dates are only dates and times are only times.

Reply | Quote

Excel keeps dates and times togeter. The date is the whole number part. The time is the fractional part.
I would go with John’s original formula, but multiply by 24*60 to convert the fraction to minutes:

=24*60*(C1+D1-A1-B1)

The safe way that John just posted uses the int function to make sure there is no hidden time, and uses the mod function to make sure there is no hidden date. I’ve attached a worksheet in case you are still confused. Date and times are tough at first. HTH –Sam

Reply | Quote

You lost me there…what formula did you use to get the result of 750?

Reply | Quote

I’d be a little careful with John’s formula in case there are dates and times buried in the displayed values. I would use:

=int(c1)+(d1-int(d1))-int(a1)-(b1-int(b1))

Just to make sure that WYSIWYG.

Reply | Quote

Or =INT(C1)+MOD(D1,1)-INT(A1)-MOD(B1,1)

Reply | Quote

Or =INT(C1)+MOD(D1,1)-INT(A1)-MOD(B1,1)

Reply | Quote

I’d be a little careful with John’s formula in case there are dates and times buried in the displayed values. I would use:

=int(c1)+(d1-int(d1))-int(a1)-(b1-int(b1))

Just to make sure that WYSIWYG.

Reply | Quote

If the date and time fields are valid Excel date and time fields (not text masquerading as time or date),

	A	B	C	D	E
1	01/01/04	9:00 PM	01/02/04	9:30 AM	=C1+D1-A1-B1

and the difference calculation in cell E1 is Custom formatted as [m]:ss. Does this help?

Reply | Quote

Chip Pearson has the best collection of Date Time information.

http://www.CPearson.com

Reply | Quote

Chip Pearson has the best collection of Date Time information.

http://www.CPearson.com

Reply | Quote