Here is a sample of the spread sheet
A B C D
E F
EMS4 unit TRANS HOSP time TRANS.TO
1 CA2004005787-1 M110B 21:11:53 21:31:22 0:19:29 PRESBYTERIAN
2 CA2004005820-1 M1113 10:19:00 10:35:00 0:16:00 BAYLOR
3 CA2004000010-1 M111A 5:05:36 5:17:35 0:11:59 PARKLAND
4 CA2004000055-1 M111A 9:44:27 9:53:09 0:08:42 ROBERT H
5 CA2004001173-1 M111A 23:54:06 0:05:25 ##### TRINITY
This is my formula =D2-C2 pasted all the way down the E column. As you
can see line on line 5 d5 is less thand c5 so I only get the ####. Need to
know something about and if < then do this I think, but I am not sure.
Thanks again.
As you can see the
--
Ron Thetford
"David McRitchie" wrote:
Hi Ron,
You probably have to format the result as you want to see it.
Did you give the correct cell addresses, normally you would have everything
on the same row.
see http://www.mvps.org/dmcritchie/excel...e.htm#timediff
If that doesn't help tell what value you have in each cell, what you
expected and what you saw.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Ron Thetford" wrote in message ...
Thank you for your reply. I tried the =B2-B1 + (B2<B1) and the mod command,
but neither seem to work. I am not very strong with formulas in excel, so I
can just tell you I copied and pasted you first example into the cell then,
modified the letter to match with the at hosp time minus the enroute to hop.
I did not understand if I need to do somehting about the true/false part of
the statement. Thanks again.
--
Ron Thetford
"JE McGimpsey" wrote:
XL stores times as fractional days, which means that 03:00:00 = 0.125
and 21:00:00 = 0.875, so "later" times can be numerically less than
"earlier" times if the times span midnight.
One way to work around this is to add 1 if the later time is less than
the earlier time. Using XL's implicit coercion of boolean TRUE/FALSE to
1/0:
=B2-B1 + (B2<B1)
another is to use the slightly more obscure MOD function:
=MOD(B2-B1,1)
In article ,
Ron Thetford wrote:
I have data from our public safety system I download straight into Excel. I
have two colums of time in the following format: 00:00:00 Of course this
is just the format it comes in as. I have for example 11:50:00 in cell A and
12:00:00 in cell B
I know I can say cell C is =(B2-B1). This works on most records but I have
the occasion where cell a is greater than cell B: Example Cell A "23:59:00
and Cell B is 00:12:00. This is cell A equals the time a ambulance left the
locaiton of a call enrourte to a hospital. Cell B is when they get to the
hospital. I need to somehow add back in 24:00:00 if cell B is less than cell
A. ( I think). Anyway I am by no mean anywhere near a lot of experience in
Excel. I think I could use it alot for reprot on the public saftety side,
but I need to pick up a lot of info. Any help is greatly appreciated. Thanks