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
--
Ron Thetford

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

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

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

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

Ron
As JE said in his earlier post, but substituting your ranges
=MOD(D2-C2,1)
copied down the range will return you the correct results

--
Regards
Roger Govier
"Ron Thetford" wrote in message
...
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 was mentioned EACH time and as described my page, you are
missing the correction which is the logical expression D1<C1
which returns True or Fales i.e. it returns 1 or 0
Time is stored as fractions of a day, so 1 will add 24 hours..

CA2004005787-1 M110B 21:11:53 21:31:22 0:19:29 PRESBYTERIAN
CA2004005820-1 M1113 10:19:00 10:35:00 0:16:00 BAYLOR
CA2004000010-1 M111A 05:05:36 05:17:35 0:11:59 PARKLAND
CA2004000055-1 M111A 09:44:27 09:53:09 0:08:42 ROBERT
CA2004001173-1 M111A 23:54:06 00:05:25 0:11:19 TRINITY

E1: =D1-C1+(D1<C1)
E2: =D2-C2+(D2<C2)
E3: =D3-C3+(D3<C3)
E4: =D4-C4+(D4<C4)
E5: =D5-C5+(D5<C5)

E1: [h]:mm:ss entered via format -- Cells -- Custom
E2: [h]:mm:ss You don't need the hour enclosed in brackets here
E3: [h]:mm:ss but you will need the hours in brackes if you were
E4: [h]:mm:ss to total the column and the time exceeded 24 hours
E5: [h]:mm:ss it would keep the total from rolling over to days and be seen.

Not only did I give you a page reference, but I pointed to a particular
section.

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

Guess I was right about you having the times on the same row.
It helps to give an example that matches the problem.

Please read the section of the webpage, I suggested, and the read over
the entire page, so that you will understand how Excel works with date
and time. You can start with my page or you can start with
Chip Pearson's page: http://www.cpearson.com/excel/datetime.htm
but I would suggest that you read both pages..

You might also look in HELP for #
it gives you some pretty good hints like having negative time.
---
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 ...
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