A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Time formula (difference of predicted and actual time)



 
 
Thread Tools Display Modes
  #1  
Old September 15th 08, 05:00 PM posted to microsoft.public.excel.misc
Deb
external usenet poster
 
Posts: 102
Default Time formula (difference of predicted and actual time)

I have a time formula in my spreedsheet. It is to calculate the difference
in a predicted time with the actual time. Time is entered as text eg. 14:00.
My formula is
=TEXT(A4-H4,"H:MM") OR
=TEXT(predicted time - actual time, "H:MM")

The formula works great if the actual time is prior to or equal to the
predicted time. However I cannot get a result for the time if the actual
time is later than the predicted time. I need the result to be -h,mm. If
you can help me accomplish this I would really appriceate it. Thank you
Ads
  #2  
Old September 15th 08, 05:16 PM posted to microsoft.public.excel.misc
Bob Phillips[_3_]
external usenet poster
 
Posts: 2,420
Default Time formula (difference of predicted and actual time)

=IF(H4>A4,"-","")&TEXT(MOD(A4-H4,1),"H:MM")

--
__________________________________
HTH

Bob

"deb" > wrote in message
...
>I have a time formula in my spreedsheet. It is to calculate the difference
> in a predicted time with the actual time. Time is entered as text eg.
> 14:00.
> My formula is
> =TEXT(A4-H4,"H:MM") OR
> =TEXT(predicted time - actual time, "H:MM")
>
> The formula works great if the actual time is prior to or equal to the
> predicted time. However I cannot get a result for the time if the actual
> time is later than the predicted time. I need the result to be -h,mm. If
> you can help me accomplish this I would really appriceate it. Thank you



  #3  
Old September 15th 08, 05:24 PM posted to microsoft.public.excel.misc
muddan madhu
external usenet poster
 
Posts: 747
Default Time formula (difference of predicted and actual time)

check it here

http://www.j-walk.com/ss/excel/usertips/tip051.htm




On Sep 15, 12:00*pm, deb > wrote:
> I have a time formula in my spreedsheet. *It is to calculate the difference
> in a predicted time with the actual time. Time is entered as text eg. 14:00. *
> *My formula is
> =TEXT(A4-H4,"H:MM") OR
> =TEXT(predicted time - actual time, "H:MM")
>
> The formula works great if the actual time is prior to or equal to the
> predicted time. *However I cannot get a result for the time if the actual
> time is later than the predicted time. *I need the result to be -h,mm. *If
> you can help me accomplish this I would really appriceate it. *Thank you


  #4  
Old September 15th 08, 05:49 PM posted to microsoft.public.excel.misc
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default Time formula (difference of predicted and actual time)

If the answer should be -3 hours, your formula gives -21 hours, Bob.

Perhaps try
=IF(H4>A4,"-","")&TEXT(ABS(A4-H4),"H:MM")
--
David Biddulph

"Bob Phillips" > wrote in message
...
> =IF(H4>A4,"-","")&TEXT(MOD(A4-H4,1),"H:MM")
>
> --
> __________________________________
> HTH
>
> Bob
>
> "deb" > wrote in message
> ...
>>I have a time formula in my spreedsheet. It is to calculate the
>>difference
>> in a predicted time with the actual time. Time is entered as text eg.
>> 14:00.
>> My formula is
>> =TEXT(A4-H4,"H:MM") OR
>> =TEXT(predicted time - actual time, "H:MM")
>>
>> The formula works great if the actual time is prior to or equal to the
>> predicted time. However I cannot get a result for the time if the actual
>> time is later than the predicted time. I need the result to be -h,mm.
>> If
>> you can help me accomplish this I would really appriceate it. Thank you

>
>



  #5  
Old September 26th 08, 01:27 AM posted to microsoft.public.excel.misc
Deb
external usenet poster
 
Posts: 102
Default Time formula (difference of predicted and actual time)

Thanks David, this works great for the late times but now it does not show
early times properly do you know what to do so that it will work either way.
Thanks again

"David Biddulph" wrote:

> If the answer should be -3 hours, your formula gives -21 hours, Bob.
>
> Perhaps try
> =IF(H4>A4,"-","")&TEXT(ABS(A4-H4),"H:MM")
> --
> David Biddulph
>
> "Bob Phillips" > wrote in message
> ...
> > =IF(H4>A4,"-","")&TEXT(MOD(A4-H4,1),"H:MM")
> >
> > --
> > __________________________________
> > HTH
> >
> > Bob
> >
> > "deb" > wrote in message
> > ...
> >>I have a time formula in my spreedsheet. It is to calculate the
> >>difference
> >> in a predicted time with the actual time. Time is entered as text eg.
> >> 14:00.
> >> My formula is
> >> =TEXT(A4-H4,"H:MM") OR
> >> =TEXT(predicted time - actual time, "H:MM")
> >>
> >> The formula works great if the actual time is prior to or equal to the
> >> predicted time. However I cannot get a result for the time if the actual
> >> time is later than the predicted time. I need the result to be -h,mm.
> >> If
> >> you can help me accomplish this I would really appriceate it. Thank you

> >
> >

>
>
>

  #6  
Old September 26th 08, 02:48 AM posted to microsoft.public.excel.misc
Deb
external usenet poster
 
Posts: 102
Default Time formula (difference of predicted and actual time)

Thank you. Very simple solution

"muddan madhu" wrote:

> check it here
>
> http://www.j-walk.com/ss/excel/usertips/tip051.htm
>
>
>
>
> On Sep 15, 12:00 pm, deb > wrote:
> > I have a time formula in my spreedsheet. It is to calculate the difference
> > in a predicted time with the actual time. Time is entered as text eg. 14:00.
> > My formula is
> > =TEXT(A4-H4,"H:MM") OR
> > =TEXT(predicted time - actual time, "H:MM")
> >
> > The formula works great if the actual time is prior to or equal to the
> > predicted time. However I cannot get a result for the time if the actual
> > time is later than the predicted time. I need the result to be -h,mm. If
> > you can help me accomplish this I would really appriceate it. Thank you

>
>

  #7  
Old September 26th 08, 07:27 AM posted to microsoft.public.excel.misc
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default Time formula (difference of predicted and actual time)

In what situations is it not working?
Please tell us what formula you've used, what numbers were in the cells
feeding into the formula, what result you got, and what result you expected.
--
David Biddulph

"deb" > wrote in message
...
> Thanks David, this works great for the late times but now it does not show
> early times properly do you know what to do so that it will work either
> way.
> Thanks again
>
> "David Biddulph" wrote:
>
>> If the answer should be -3 hours, your formula gives -21 hours, Bob.
>>
>> Perhaps try
>> =IF(H4>A4,"-","")&TEXT(ABS(A4-H4),"H:MM")
>> --
>> David Biddulph
>>
>> "Bob Phillips" > wrote in message
>> ...
>> > =IF(H4>A4,"-","")&TEXT(MOD(A4-H4,1),"H:MM")
>> >
>> > --
>> > __________________________________
>> > HTH
>> >
>> > Bob
>> >
>> > "deb" > wrote in message
>> > ...
>> >>I have a time formula in my spreedsheet. It is to calculate the
>> >>difference
>> >> in a predicted time with the actual time. Time is entered as text eg.
>> >> 14:00.
>> >> My formula is
>> >> =TEXT(A4-H4,"H:MM") OR
>> >> =TEXT(predicted time - actual time, "H:MM")
>> >>
>> >> The formula works great if the actual time is prior to or equal to the
>> >> predicted time. However I cannot get a result for the time if the
>> >> actual
>> >> time is later than the predicted time. I need the result to be -h,mm.
>> >> If
>> >> you can help me accomplish this I would really appriceate it. Thank
>> >> you
>> >
>> >

>>
>>
>>



  #8  
Old September 26th 08, 04:55 PM posted to microsoft.public.excel.misc
Deb
external usenet poster
 
Posts: 102
Default Time formula (difference of predicted and actual time)

I am so sorry my mistake. I'm not sure what I did wrong, but I reworked it
and the formula you provided worked perfect. Thank you so much

"David Biddulph" wrote:

> In what situations is it not working?
> Please tell us what formula you've used, what numbers were in the cells
> feeding into the formula, what result you got, and what result you expected.
> --
> David Biddulph
>
> "deb" > wrote in message
> ...
> > Thanks David, this works great for the late times but now it does not show
> > early times properly do you know what to do so that it will work either
> > way.
> > Thanks again
> >
> > "David Biddulph" wrote:
> >
> >> If the answer should be -3 hours, your formula gives -21 hours, Bob.
> >>
> >> Perhaps try
> >> =IF(H4>A4,"-","")&TEXT(ABS(A4-H4),"H:MM")
> >> --
> >> David Biddulph
> >>
> >> "Bob Phillips" > wrote in message
> >> ...
> >> > =IF(H4>A4,"-","")&TEXT(MOD(A4-H4,1),"H:MM")
> >> >
> >> > --
> >> > __________________________________
> >> > HTH
> >> >
> >> > Bob
> >> >
> >> > "deb" > wrote in message
> >> > ...
> >> >>I have a time formula in my spreedsheet. It is to calculate the
> >> >>difference
> >> >> in a predicted time with the actual time. Time is entered as text eg.
> >> >> 14:00.
> >> >> My formula is
> >> >> =TEXT(A4-H4,"H:MM") OR
> >> >> =TEXT(predicted time - actual time, "H:MM")
> >> >>
> >> >> The formula works great if the actual time is prior to or equal to the
> >> >> predicted time. However I cannot get a result for the time if the
> >> >> actual
> >> >> time is later than the predicted time. I need the result to be -h,mm.
> >> >> If
> >> >> you can help me accomplish this I would really appriceate it. Thank
> >> >> you
> >> >
> >> >
> >>
> >>
> >>

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Time difference calculations, daylight savings time, Excel Tim Excel Discussion (Misc queries) 1 December 28th 06 04:18 PM
Converting time formats into actual time(minutes) LeighM Excel Discussion (Misc queries) 2 October 30th 06 05:15 AM
Negative time should be allowed in Excel, eg time difference Bengt-Inge Larsson Excel Discussion (Misc queries) 2 October 13th 05 12:59 PM
How to sync my received time with the actual time? NJD Excel Discussion (Misc queries) 3 August 22nd 05 08:11 AM
Time / Formula to look at time difference carl Excel Worksheet Functions 5 November 8th 04 06:59 PM


All times are GMT +1. The time now is 07:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.