time calculation with military time

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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> > 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > > Ron Thetford <rthetford@comcast.net> 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,
Okay, I always check afterwards that it did go down as far as I wanted.
I have a macro based on a suggestion from Tom Ogilvy that you find
on my toolbars.htm page. Bug you find the bottom fairly quickly
with Ctrl+End then place the range into the name box to the
left of the formula bar as E2:E3002
where E2 has your formula then use Ctrl+D to fill down.
For more information of on that I should have pointed you to my
http://www.mvps.org/dmcritchie/excel/filldown.htm
page. Rather than just stating in your case you can probably...
--
---
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" <Ron Thetford@discussions.microsoft.com> wrote in message news:118ACAEC-C6B9-4970-B2BA-418C0C7F92EF@microsoft.com...
> Thank you as that worked except for one thing. My list has over 3000 row,
> and some of d1 is blank or has oo:oo:oo in it, when I put in the first
> formula and then double click on th box to paste it down the colum it stops
> where ever the d1 it blank or has 00:00:00 as the arrive/greater time cell.

Thank you as that worked except for one thing. My list has over 3000 row,
and some of d1 is blank or has oo:oo:oo in it, when I put in the first
formula and then double click on th box to paste it down the colum it stops
where ever the d1 it blank or has 00:00:00 as the arrive/greater time cell.


"David McRitchie" wrote:

> 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" <rthetford@comcast.net> wrote in message news:367F2F9D-686D-4106-9DA0-EA6346411ED3@microsoft.com...
> > 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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> > > > 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > > > > Ron Thetford <rthetford@comcast.net> 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
> > > > >
> > >
> > >
> > >
>
>
>

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" <rthetford@comcast.net> wrote in message news:367F2F9D-686D-4106-9DA0-EA6346411ED3@microsoft.com...
> 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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> > > 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > > > Ron Thetford <rthetford@comcast.net> 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" <rthetford@comcast.net> wrote in message
news:367F2F9D-686D-4106-9DA0-EA6346411ED3@microsoft.com...
> 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.
>

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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > Ron Thetford <rthetford@comcast.net> 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> Ron Thetford <rthetford@comcast.net> 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
>

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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
Ron Thetford <rthetford@comcast.net> 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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > Ron Thetford <rthetford@comcast.net> 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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> > 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > > Ron Thetford <rthetford@comcast.net> 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
> > >
>
>
>

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

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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> Ron Thetford <rthetford@comcast.net> 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" <rthetford@comcast.net> wrote in message
news:367F2F9D-686D-4106-9DA0-EA6346411ED3@microsoft.com...
> 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" <rthetford@comcast.net> wrote in message news:367F2F9D-686D-4106-9DA0-EA6346411ED3@microsoft.com...
> 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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> > > 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > > > Ron Thetford <rthetford@comcast.net> 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 as that worked except for one thing. My list has over 3000 row,
and some of d1 is blank or has oo:oo:oo in it, when I put in the first
formula and then double click on th box to paste it down the colum it stops
where ever the d1 it blank or has 00:00:00 as the arrive/greater time cell.


"David McRitchie" wrote:

> 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" <rthetford@comcast.net> wrote in message news:367F2F9D-686D-4106-9DA0-EA6346411ED3@microsoft.com...
> > 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" <rthetford@comcast.net> wrote in message news:E61E8DE9-2B36-4442-B704-B52CD45F1FD0@microsoft.com...
> > > > 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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
> > > > > Ron Thetford <rthetford@comcast.net> 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
> > > > >
> > >
> > >
> > >
>
>
>

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 <60A8070D-E156-4830-A4AF-21DA6F395CC1@microsoft.com>,
Ron Thetford <rthetford@comcast.net> 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,
Okay, I always check afterwards that it did go down as far as I wanted.
I have a macro based on a suggestion from Tom Ogilvy that you find
on my toolbars.htm page. Bug you find the bottom fairly quickly
with Ctrl+End then place the range into the name box to the
left of the formula bar as E2:E3002
where E2 has your formula then use Ctrl+D to fill down.
For more information of on that I should have pointed you to my
http://www.mvps.org/dmcritchie/excel/filldown.htm
page. Rather than just stating in your case you can probably...
--
---
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" <Ron Thetford@discussions.microsoft.com> wrote in message news:118ACAEC-C6B9-4970-B2BA-418C0C7F92EF@microsoft.com...
> Thank you as that worked except for one thing. My list has over 3000 row,
> and some of d1 is blank or has oo:oo:oo in it, when I put in the first
> formula and then double click on th box to paste it down the colum it stops
> where ever the d1 it blank or has 00:00:00 as the arrive/greater time cell.