edsco
|
| posted on 13/1/14 at 05:07 PM |
|
|
Excel help please...Time functions
Hi All
Hoping someone can shine some light on this for me.....as i am little bamboozled!
Trying to calculate the variance in time and display the end result as shown below:
EG.
Planned Activity Time = 14:25
Actual Activity Time = 14:00
So 14:25 - 14:00 = 25 min
Equally; if Planned Activity was 14:00 and the Actual 14:25 this too equals 25 min but would want to show it as '-25 min'.
I am looking for a formula that will post as results both negative variances and positive. All i can achieve at the moment the is a positive
number.
Does this make sense?
Heres hoping for a flash of inspiration!!
Ed
edsco
|
|
|
|
|
theprisioner
|
| posted on 13/1/14 at 06:18 PM |
|
|
 
Time
Enter 0 into two cells A1, B1
Format the cells as Time 00:00:00
Change time to 14:00 and 14:25 in the cells
Take a third cell (do not change format) then add A1 + B1 or A1 - B1 does not matter
http://sylvabuild.blogspot.com/
http://austin7special.blogspot.co.uk/
|
|
|
edsco
|
| posted on 13/1/14 at 06:52 PM |
|
|
Hi
Thanks for the reply. I understand what you have outlined, but i cannot get it to show as 'minus' min....
If i follow your guidance on formatting 14:25:00 - 14:00:00 results in 00:25:00 and not -00:25:00 as you have in your example.
Any ideas what i have done wrong or you have done differently?
ta
edsco
|
|
|
Barkalarr
|
| posted on 13/1/14 at 06:55 PM |
|
|
Can't you then multiply by -1 ?
|
|
|
Krismc
|
| posted on 13/1/14 at 06:57 PM |
|
|
Because that is what the equation is asking for, you are asking for the difference between the 2 times therefore it is a positive number that is the
shown.
You can express as a negative, I have a equation at work and ill post up tomorrow it was so long ago when i set the field I cant remember. I use it
for project planning.
Kris
Built, Ivaed, Drove and now Sold - 2011 MNR VORTX RT+ 2000cc Zetec on R1 Throttle boddies.
|
|
|
edsco
|
| posted on 13/1/14 at 07:07 PM |
|
|
Kris
That would be most helpful if you would share that little nugget of information.
Barkalarr: Hmmm no not really. I need a formula that will give both negative and positive responses. By conditioning it with '-1' you
end up skewing the results, assuming it works.
Im sure its a combination of formatting of cells and a fancy formula but i just cant see the wood for all the flippin trees!! lol
ta
edsco
|
|
|
omega0684
|
| posted on 13/1/14 at 07:16 PM |
|
|
Hi Ed.
Simply enter the two times into cell A1 & B1 and in cell C1 copy and paste the following formula (obviously make sure that you format cell C1 as
time hh:mm)
=IF(A1<B1,TEXT(ABS(B1-A1),"-hh:mm" ), A1-B1)
eg
A1 = 14:00, B1 = 14:25, C1 = -00:25
Job Done!
Let me know how you get on?
[Edited on 13/1/14 by omega0684]
I love Pinto's, even if i did get mine from P&O!
|
|
|
edsco
|
| posted on 13/1/14 at 07:46 PM |
|
|
BOOOOM! There it is.
cheers fella!
edsco
|
|
|
omega0684
|
| posted on 13/1/14 at 07:51 PM |
|
|
OOOOOOSH! 
Happy to help! 
I love Pinto's, even if i did get mine from P&O!
|
|
|
