Excel Time Calculation Formula - Please Help!! :)
Hi There,
I am trying to calculate user avaliabilty for telephone stats..
The data I have avaliable is A2,B2,C2,D2.
Firstly I am trying to work out the time difference. In E2 i have a formula, just simply D2-C2. If the result is positive it gives a result, but if negative just puts hundreds of #?
I need to get to the % avaliabilty. This can be over 100%, which would be the case for the second row of data where the user went over thier avaliabilty.
| A2 | B2 | C2 | D2 | E2 | F2 |
| Date | Name | Total Time Available | Expected Availability | Time Difference | % Avaliability |
| 01/05/2009 | Ashley | 07:10:00 | 07:20:00 | 00:10:00 | |
| 02/05/2009 | Ashley | 08:10:00 | 07:20:00 | ############## |
Any ideas or help please?
Many Thanks.
% availability
In cell F3 put this formula:
"=(C2/D2)"
You should end up with 97.73% if you format the cell to percent with 2 decimal places.
In cell F4 copy formula from above.
You should end up with 111.36%, this is expected since "Total Time Available" is greater than "Expected Availability". If you do as previous comment and add the ABS to your formula
("=ABS(C2/D2").
help with formula
to avoid the ######## wrap the formula with ABS
so for Example:
=A1-A2
would be:
=ABS(A1-A2)
ABS returns the absolute value regardless of sign.