Printable Version | Subscribe | Add to Favourites
New Topic New Poll New Reply
Author: Subject: Excel help please...Time functions
edsco

posted on 13/1/14 at 05:07 PM Reply With Quote
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

View User's Profile View All Posts By User U2U Member
theprisioner

posted on 13/1/14 at 06:18 PM Reply With Quote
Time
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/

View User's Profile Visit User's Homepage View All Posts By User U2U Member
edsco

posted on 13/1/14 at 06:52 PM Reply With Quote
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

View User's Profile View All Posts By User U2U Member
Barkalarr
Contributor






Posts 942
Registered 14/3/12
Location Essex
Member Is Offline

Photo Archive Go!
Building: Caterham R300, Caterham Roadsport,Indy R1,Indy Zet

posted on 13/1/14 at 06:55 PM Reply With Quote
Can't you then multiply by -1 ?
View User's Profile View All Posts By User U2U Member
Krismc

posted on 13/1/14 at 06:57 PM Reply With Quote
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.

View User's Profile View All Posts By User U2U Member
edsco

posted on 13/1/14 at 07:07 PM Reply With Quote
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

View User's Profile View All Posts By User U2U Member
omega0684

posted on 13/1/14 at 07:16 PM Reply With Quote
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!

View User's Profile E-Mail User Visit User's Homepage View All Posts By User U2U Member
edsco

posted on 13/1/14 at 07:46 PM Reply With Quote
BOOOOM! There it is.

cheers fella!





edsco

View User's Profile View All Posts By User U2U Member
omega0684

posted on 13/1/14 at 07:51 PM Reply With Quote
OOOOOOSH!

Happy to help!





I love Pinto's, even if i did get mine from P&O!

View User's Profile E-Mail User Visit User's Homepage View All Posts By User U2U Member

New Topic New Poll New Reply


go to top






Website design and SEO by Studio Montage

All content © 2001-16 LocostBuilders. Reproduction prohibited
Opinions expressed in public posts are those of the author and do not necessarily represent
the views of other users or any member of the LocostBuilders team.
Running XMB 1.8 Partagium [© 2002 XMB Group] on Apache under CentOS Linux
Founded, built and operated by ChrisW.