Time Values

  • Thread starter Thread starter lee6553
  • Start date Start date
L

lee6553

Hello,
I have 2 problems. A1 = 23:30 (accident time) and B1 = 8:50 (repor
time). If I add I get the correct difference in hour and minutes. Bu
in A2 = 16:30 and B2=17:50, I have to substract b-a to get the correc
difference. I have 4 years of data and I'm trying to make this muc
simpler that going line by line. Can you help?

The second part is some time is entered as 1500 instead of 15:0
military time. Is there a way to make that read military time. Trie
formatting but it comes up to 0:00

Thank for your help.
Le
 
Hello,
I have 2 problems. A1 = 23:30 (accident time) and B1 = 8:50 (report
time). If I add I get the correct difference in hour and minutes. But
in A2 = 16:30 and B2=17:50, I have to substract b-a to get the correct
difference. I have 4 years of data and I'm trying to make this much
simpler that going line by line. Can you help?
Click to expand...

If you add A1+B1 in your first example, and format the result as time, you do
NOT get the correct difference. You get 8:20 and it should be 9:20

Assuming the report time comes later than the accident time, then the way to
get the correct difference is =B1-A1. Your problem occurs because the time
spans midnight. There are two possible solutions.

1. Include the date in the time stamp. You will have to do this anyway if the
time frame is greater than 24 hours.

2. If the time span will always be less than 24 hours, you can use the
formula: =B1-A1+(A1>B1)

The second part is some time is entered as 1500 instead of 15:00
military time. Is there a way to make that read military time. Tried
formatting but it comes up to 0:00
Click to expand...

=TIME(INT(A1/100),MOD(A1,100),0)

will do the conversion.

If all times are entered as either Excel times (e.g. 16:30) or as a number
(e.g. 1630), then the Excel times will be less than one (1) and you can use
this in a formula to decide if you need to convert it or not.

For example:

=IF(B1<1,B1,TIME(INT(B1/100),MOD(B1,100),0))-
IF(A1<1,A1,TIME(INT(A1/100),MOD(A1,100),0))+
(IF(B1<1,B1,TIME(INT(B1/100),MOD(B1,100),0))
<IF(A1<1,A1,TIME(INT(A1/100),MOD(A1,100),0)))



--ron
 
If you want the amount of time elapsed between the accident and report time,
you won't get that by adding! You MUST subtract the accident time from the
report time. To handle the problem of the clock crossing midnight, as it does
in your first example, write ALL of your formulas as

=MOD(B1-A1,1)
or

=B1-A1+(B1<A1)

As for times entered without the colon, to get Excel to convert them to a time
(15:00 is translated to the value 15/24), you have to use a formula like

=TIME(INT(A1)/100,MOD(A1,100),0)

or

=IF(A1>99,TIMEVALUE(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0),TIME(0,A1,0))
 
Back
Top