Remember Me?

#1
September 15th 08, 05:00 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 102
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

#2
September 15th 08, 05:16 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 2,420
Time formula (difference of predicted and actual time)

=IF(H4A4,"-","")&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
September 15th 08, 05:24 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Mar 2008 Posts: 747
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
September 15th 08, 05:49 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651
Time formula (difference of predicted and actual time)

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

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

"Bob Phillips" wrote in message
...
=IF(H4A4,"-","")&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
September 26th 08, 01:27 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 102
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(H4A4,"-","")&TEXT(ABS(A4-H4),"H:MM")
--
David Biddulph

"Bob Phillips" wrote in message
...
=IF(H4A4,"-","")&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
September 26th 08, 02:48 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 102
Time formula (difference of predicted and actual time)

Thank you. Very simple solution

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
September 26th 08, 07:27 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651
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(H4A4,"-","")&TEXT(ABS(A4-H4),"H:MM")
--
David Biddulph

"Bob Phillips" wrote in message
...
=IF(H4A4,"-","")&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
September 26th 08, 04:55 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 102
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(H4A4,"-","")&TEXT(ABS(A4-H4),"H:MM")
--
David Biddulph

"Bob Phillips" wrote in message
...
=IF(H4A4,"-","")&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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Tim Excel Discussion (Misc queries) 1 December 28th 06 05:18 PM LeighM Excel Discussion (Misc queries) 2 October 30th 06 06:15 AM Bengt-Inge Larsson Excel Discussion (Misc queries) 2 October 13th 05 12:59 PM NJD Excel Discussion (Misc queries) 3 August 22nd 05 08:11 AM carl Excel Worksheet Functions 5 November 8th 04 07:59 PM

All times are GMT +1. The time now is 12:17 PM.