# Thread: Calculating times from day to day (Excel 2002)

1. ## Calculating times from day to day (Excel 2002)

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

2. ## Re: Calculating times from day to day (Excel 2002)

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

3. ## Re: Calculating times from day to day (Excel 2002)

Sorry, meant to post that as a formula:

=C1+D1-A1-B1

4. ## Re: Calculating times from day to day (Excel 2002)

If the date and time fields are valid Excel date and time fields (not text masquerading as time or date), <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>1</td><td align=right>01/01/04</td><td align=right>9:00 PM</td><td align=right>01/02/04</td><td align=right>9:30 AM</td><td align=right>=C1+D1-A1-B1</td></table>and the difference calculation in cell E1 is Custom formatted as [m]:ss. Does this help?

5. ## Re: Calculating times from day to day (Excel 2002)

Chip Pearson has the best collection of Date Time information.

www.CPearson.com

6. ## Re: Calculating times from day to day (Excel 2002)

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.

7. ## Re: Calculating times from day to day (Excel 2002)

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

8. ## Re: Calculating times from day to day (Excel 2002)

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:
<pre>=24*60*(C1+D1-A1-B1)</pre>

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

9. ## Re: Calculating times from day to day (Excel 2002)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•