hi guys,
i am trying to get the time difference and convert it to percentage but i have no idea how to do it...like if time is 11:30 AM minus 12:00 PM, then this gives me a #value result instead...and how do i get the percentage for this? attached is a sample workbook..
regards,
stoey
Last edited by stoey; 09-23-2011 at 02:23 PM.
Your formula in c2 refers to A1 which contains text - hence #VALUE! error - change to this
=(B2-A2)*24
Presumably M2 should be
=SUM(C2,G2,K2)
so try this for N2
=IF(M2>1.25,0%,100%)
Audere est facere
hi there daddy,
i tried your solution and it worked perfectly however after a couple of tweaking and substituting the formula to another solution, results doesn't seem becomes right...attached is a sample of the workbook
regards,
stoey
Why are H2 and L2 wrong - the results seem consistent with the formulas - can you describe in words what you want the formula to do, is it possible you need >= rather than > ?
For N2 try
=IF(AND(D2=100%,H2=100%,L2=100%),100%,0%)
hi daddy,
that im not sure why it doesn't shows consistent results though they have the same formula...in this formula:
=IF(G2>0.25,0%,100%) - this should display 100% if the results are less than or equal to 0.25 ...i tested using exact result of 0.25 but it gives me a 0% remark in which it should be 100%
the other formula which is almost the same as above also gives wrong results
=IF(K2>0.5,0%,100%) this should display 100% if the result is equal or less than 0.5...tested this as well using exact 0.5 result but it displays 0%
i dont really get it why it works on the on the first part just like in my sample attached file but doesnt work on the other...
regards,
stoey
If you format G2 to show 15 decimal places you can see that the number in there is slightly larger than 0.25. Try using ROUND in G2, e.g. change to this
=ROUND((F2-E2)*24,9)
then the H2 formula should work as required. ROUND the other cells in the same wqay
alright! things are working perfectly now...final question is what does round do and likewise what does 9 mean in this formula:
=ROUND((F2-E2)*24,9)
In this case you are rounding to 9 decimal places - that makes no practical difference to the result but it gets rid of the small decimal places that were throwing out your formulas
i see...just what i expected...thanks a lot!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks