Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Converting time formats into actual time(minutes) | Excel Discussion (Misc queries) | |||
Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) | |||
How to sync my received time with the actual time? | Excel Discussion (Misc queries) | |||
Time / Formula to look at time difference | Excel Worksheet Functions |