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

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

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

Can't you then multiply by -1 ?

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

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

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]

BOOOOM! There it is.

cheers fella!

OOOOOOSH!

Happy to help!