| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Another Time Calulation
Ok...first Thanks to JLatham for my earlier question! Now I have another one...
How would I go about doing this...I am calulating time with this formula: =IF(D16<C16,1-ABS(D16-C16),D16-C16) works great (thanks again JLatham)... Senarioa A: I get the reults form the above formlua in cell F16...which for this example lets say is 2:00 (11:00 PM to 1:00 AM = 2:00) In cell F15 I would like to state this: If cell F16 (above) is less than (<) 2:00, subtract the value in cell F16 from 2:00. So in other words, something like this...=IF(F16<2, F16-2)....I just can't seem to get the time format correct in this formula. Any suggestins again will be greatly appreciated. Thank you in advance for your time. -- Randy Street Rancho Cucamonga, CA |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another Time Calulation
1 represents a full day. So to get hours, divide by 24. So do your
comparisons to 2/24 instead of 2. "Randy" wrote: Ok...first Thanks to JLatham for my earlier question! Now I have another one... How would I go about doing this...I am calulating time with this formula: =IF(D16<C16,1-ABS(D16-C16),D16-C16) works great (thanks again JLatham)... Senarioa A: I get the reults form the above formlua in cell F16...which for this example lets say is 2:00 (11:00 PM to 1:00 AM = 2:00) In cell F15 I would like to state this: If cell F16 (above) is less than (<) 2:00, subtract the value in cell F16 from 2:00. So in other words, something like this...=IF(F16<2, F16-2)....I just can't seem to get the time format correct in this formula. Any suggestins again will be greatly appreciated. Thank you in advance for your time. -- Randy Street Rancho Cucamonga, CA |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another Time Calulation
Thank you b....however still returns ####### value....here is what I have:
=IF(F16<2/24, F16-2/24)...is this what you were referring to? I also have the format of the cell set to "h" (without the quotes of course) -- Randy Street Rancho Cucamonga, CA "bpeltzer" wrote: 1 represents a full day. So to get hours, divide by 24. So do your comparisons to 2/24 instead of 2. "Randy" wrote: Ok...first Thanks to JLatham for my earlier question! Now I have another one... How would I go about doing this...I am calulating time with this formula: =IF(D16<C16,1-ABS(D16-C16),D16-C16) works great (thanks again JLatham)... Senarioa A: I get the reults form the above formlua in cell F16...which for this example lets say is 2:00 (11:00 PM to 1:00 AM = 2:00) In cell F15 I would like to state this: If cell F16 (above) is less than (<) 2:00, subtract the value in cell F16 from 2:00. So in other words, something like this...=IF(F16<2, F16-2)....I just can't seem to get the time format correct in this formula. Any suggestins again will be greatly appreciated. Thank you in advance for your time. -- Randy Street Rancho Cucamonga, CA |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another Time Calulation
The 2/24 is correct, but your logic is generating a negative.... if the time
elapsed is less than two hours, subtract two hours. I think you want =IF(F16<2/24,2/24-F16... That is, if the time is less than two hours, subtract it from two hours. "Randy" wrote: Thank you b....however still returns ####### value....here is what I have: =IF(F16<2/24, F16-2/24)...is this what you were referring to? I also have the format of the cell set to "h" (without the quotes of course) -- Randy Street Rancho Cucamonga, CA "bpeltzer" wrote: 1 represents a full day. So to get hours, divide by 24. So do your comparisons to 2/24 instead of 2. "Randy" wrote: Ok...first Thanks to JLatham for my earlier question! Now I have another one... How would I go about doing this...I am calulating time with this formula: =IF(D16<C16,1-ABS(D16-C16),D16-C16) works great (thanks again JLatham)... Senarioa A: I get the reults form the above formlua in cell F16...which for this example lets say is 2:00 (11:00 PM to 1:00 AM = 2:00) In cell F15 I would like to state this: If cell F16 (above) is less than (<) 2:00, subtract the value in cell F16 from 2:00. So in other words, something like this...=IF(F16<2, F16-2)....I just can't seem to get the time format correct in this formula. Any suggestins again will be greatly appreciated. Thank you in advance for your time. -- Randy Street Rancho Cucamonga, CA |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another Time Calulation
=IF(F16<2/24, F16-2/24) will give an output of FALSE if F16 is greater than
or equal to 2/24, but if F16<2/24 the result will be negative, and if you format the result as a date or time, Excel doesn't like negative values. If you want to show negative times, change to 1904 date system, but be wary of other consequential changes. -- David Biddulph "Randy" wrote in message ... Thank you b....however still returns ####### value....here is what I have: =IF(F16<2/24, F16-2/24)...is this what you were referring to? I also have the format of the cell set to "h" (without the quotes of course) -- Randy Street Rancho Cucamonga, CA "bpeltzer" wrote: 1 represents a full day. So to get hours, divide by 24. So do your comparisons to 2/24 instead of 2. "Randy" wrote: Ok...first Thanks to JLatham for my earlier question! Now I have another one... How would I go about doing this...I am calulating time with this formula: =IF(D16<C16,1-ABS(D16-C16),D16-C16) works great (thanks again JLatham)... Senarioa A: I get the reults form the above formlua in cell F16...which for this example lets say is 2:00 (11:00 PM to 1:00 AM = 2:00) In cell F15 I would like to state this: If cell F16 (above) is less than (<) 2:00, subtract the value in cell F16 from 2:00. So in other words, something like this...=IF(F16<2, F16-2)....I just can't seem to get the time format correct in this formula. Any suggestins again will be greatly appreciated. Thank you in advance for your time. -- Randy Street Rancho Cucamonga, CA |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another Time Calulation
But, of course if you want to see the answer not as a time, but as a number
of hours, you can change your =IF(F16<2/24, F16-2/24) to =IF(F16<2/24, 24*F16-2) and format the answer as Number or General, not Time, then you don't need to worry about changing the date system. -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =IF(F16<2/24, F16-2/24) will give an output of FALSE if F16 is greater than or equal to 2/24, but if F16<2/24 the result will be negative, and if you format the result as a date or time, Excel doesn't like negative values. If you want to show negative times, change to 1904 date system, but be wary of other consequential changes. -- David Biddulph "Randy" wrote in message ... Thank you b....however still returns ####### value....here is what I have: =IF(F16<2/24, F16-2/24)...is this what you were referring to? I also have the format of the cell set to "h" (without the quotes of course) -- Randy Street Rancho Cucamonga, CA "bpeltzer" wrote: 1 represents a full day. So to get hours, divide by 24. So do your comparisons to 2/24 instead of 2. "Randy" wrote: Ok...first Thanks to JLatham for my earlier question! Now I have another one... How would I go about doing this...I am calulating time with this formula: =IF(D16<C16,1-ABS(D16-C16),D16-C16) works great (thanks again JLatham)... Senarioa A: I get the reults form the above formlua in cell F16...which for this example lets say is 2:00 (11:00 PM to 1:00 AM = 2:00) In cell F15 I would like to state this: If cell F16 (above) is less than (<) 2:00, subtract the value in cell F16 from 2:00. So in other words, something like this...=IF(F16<2, F16-2)....I just can't seem to get the time format correct in this formula. Any suggestins again will be greatly appreciated. Thank you in advance for your time. -- Randy Street Rancho Cucamonga, CA |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another Time Calulation
Ah yes....that did the trick! Thank you again for your help with this....you
too are a ROCK STAR! -- Randy Street Rancho Cucamonga, CA "bpeltzer" wrote: The 2/24 is correct, but your logic is generating a negative.... if the time elapsed is less than two hours, subtract two hours. I think you want =IF(F16<2/24,2/24-F16... That is, if the time is less than two hours, subtract it from two hours. "Randy" wrote: Thank you b....however still returns ####### value....here is what I have: =IF(F16<2/24, F16-2/24)...is this what you were referring to? I also have the format of the cell set to "h" (without the quotes of course) -- Randy Street Rancho Cucamonga, CA "bpeltzer" wrote: 1 represents a full day. So to get hours, divide by 24. So do your comparisons to 2/24 instead of 2. "Randy" wrote: Ok...first Thanks to JLatham for my earlier question! Now I have another one... How would I go about doing this...I am calulating time with this formula: =IF(D16<C16,1-ABS(D16-C16),D16-C16) works great (thanks again JLatham)... Senarioa A: I get the reults form the above formlua in cell F16...which for this example lets say is 2:00 (11:00 PM to 1:00 AM = 2:00) In cell F15 I would like to state this: If cell F16 (above) is less than (<) 2:00, subtract the value in cell F16 from 2:00. So in other words, something like this...=IF(F16<2, F16-2)....I just can't seem to get the time format correct in this formula. Any suggestins again will be greatly appreciated. Thank you in advance for your time. -- Randy Street Rancho Cucamonga, CA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CALULATION OF DURATION | Excel Discussion (Misc queries) | |||
| Another Time Calulation Question - Sorry. | New Users to Excel | |||
| how do I hide a calulation error in cells/table #div/0! | Excel Discussion (Misc queries) | |||
| Getpivot calulation | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Linear Mode
