Elapsed time calculation
Posted by Wendy Liz on July 11, 2001 1:19 PM
I tried the suggestions for calculating time but it didn't work. I need a formula to calculate the time elapsed on a worksheet. In other words, Cell A has military time 08:00 and Cell B has 09:13 Cell C should show me that 1 hr and 13 minutes was spent on a task. Any suggestions ?
Posted by Aladin Akyurek on July 11, 2001 2:28 PM
I hope I didn't suggest what follows alredy:
In C2 enter: =B2+(B2 < A2)-A2
Custom format C2 as [h]:mm
Aladin
Posted by Julio S on July 11, 2001 2:54 PM
Hi Aladin:
I know you were explaining something else, but could
you please tell me the logic this formula follows:
=B2+(B2 < A2)-A2
I had not seen formulas with "<" in them...
Thanks!
Posted by Joe Was on July 11, 2001 3:15 PM
Re-format with:
From the menu: (Format-Cells-Number-Custom {in the "Type:"} add;
[h]"hr ":mm"min"
Then use the formula:
time-cell{2}+(time-cell{2}<time-cell{1})-time-cell{1}
in any cell formatted with the custom format above.
You will get if cell{1} is 8:00 and cell{2} is 9:13. 1hr 13min as the result. JSW
Posted by Joe Was on July 11, 2001 3:19 PM
Re: Re-format with: (use this one somehow the one above was corrupted?
Posted by Aladin Akyurek on July 11, 2001 3:46 PM
If it's the case that B2 is smaller than A2 while later on the time axis (e.g., A2=10:30 AM and B2=1:30 AM), =B2-A2 would not produce the desired result. Whence B2 < A2, which is a logical expression that will produce either TRUE or FALSE. An attempt to substract from or to add to a logical value coerces Excel to treat FALSE as 0 and TRUE as 1. If B2 is smaller than A2, the equation will become:
=SerialNumberRepresentingB2 + 1 - SerialNumberRepresentingA2,
In A2 enter: 10:30
In B2 enter: 5:30
In C2 enter: the suggested formula.
Go the formula bar, select a term and hit F9. Do this for each term including B2 < A2 term. You'll see how the formula is really evaluated by Excel.
BTW, try also: =SUM(B6 < A6,A6:B6), where A6=3 and B6=2.
Aladin
================
Posted by Julio S on July 11, 2001 4:14 PM
Wow...A very interesting and useful way to save on
the use of "IF's". The "F9" Function to evaluate
the components of the formula is pretty cool...
Excellent... Thanks for all your time and help!
Julio
Posted by Don W on July 12, 2001 6:39 AM
I,m not an expert, but the way I do it is as follows: Col A1 put in 8:00, Col B1 type in formula =now(), format B1 - custom h:mm, col C1 tpe formula =b1-a1. format c1 also as h:mm.
anytime you hit the f9 key, it will update the time in b1, and amount of time in c1.
Posted by DonW on July 12, 2001 8:47 AM
I don't know if you solved your problem yet, but after reading all your replies, I think the easest way to get the results you are seeking is the following:
A1 08:00
B1 type "=now()"
B1 format custom h:mm (important this is done after the =now() formula, otherwise you will get the date & time)
C1 format custom h:mm
C1 =b1-a1
Afer you do this, C1 will give the correct time spent, and is updated whenever you hit the F9 key.
Posted by Aladin Akyurek on July 12, 2001 9:00 AM
Don,
=B1-A1 and =B1+(B1 < A1)-A1 are equivalent in case B1 is never smaller than A1.
Aladin
Posted by DonW on July 12, 2001 1:30 PM
Aladin; I tried your formula using a later time in A1, and I get the same wrong answer in C1.
I put 17:23 in col A1, and 16:23 in B1, and received an answer of 23:00 when in fact it should be 25:00. I received the same answer with both B1-A1, & B1+(B1<A1)-A1. Why???
Don
Posted by Aladin Akyurek on July 12, 2001 2:00 PM
DON, YOU NEED TO RESPECT THE LOGIC OF ENTERING TIMES: A1 should contain a time point earlier than the time point in B1. This is just a convention. We have to stick to it.
A1=10:30 [AM], B1=19:45 [PM] (Military time; strictly speaking, we should write: 1030 and 1945).
A1=13:45 PM, B1=04:30 (this means over midnight; the longer formula can handle this, the shorter not).
Following the convention, your example should be all PM values:
A1=17:23 [PM], B1=16:23 [PM]
The longer formula produces: 23:00, which is correct.
Don't you think?
Aladin
============
:Don, : =B1-A1 and =B1+(B1 < A1)-A1 are equivalent in case B1 is never smaller than A1. : Aladin :
