Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ron Thetford
 
Posts: n/a
Default time calculation with military time

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
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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

  #3   Report Post  
Ron Thetford
 
Posts: n/a
Default

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


  #4   Report Post  
David McRitchie
 
Posts: n/a
Default

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




  #5   Report Post  
Ron Thetford
 
Posts: n/a
Default

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






  #6   Report Post  
Roger Govier
 
Posts: n/a
Default

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.



  #7   Report Post  
David McRitchie
 
Posts: n/a
Default

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






  #8   Report Post  
Ron Thetford
 
Posts: n/a
Default

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" 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







  #9   Report Post  
David McRitchie
 
Posts: n/a
Default

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 wrote in message ...
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time Calculation in Minutes only ? pgcam Excel Worksheet Functions 2 May 20th 05 07:35 PM
Time Calculation in Minutes only ? pgcam Excel Worksheet Functions 1 May 20th 05 04:46 PM
Time Sheet Calculation Help Needed! sax30 Excel Worksheet Functions 2 April 26th 05 08:08 PM
Time Calculation chintu49 Excel Worksheet Functions 2 February 16th 05 03:55 PM
Time calculation. shital shah Excel Worksheet Functions 2 January 20th 05 12:25 PM


All times are GMT +1. The time now is 12:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"