|
If Statement to subtract €śtime€ť if condition met
First thanks for all the great info to get me this far! Think Im close, but
cannot seem to get the time format to show properly. Help? Trying to subtract €ślunch period€ť if partial day leave includes lunch start/stop times €“ the second row contains the formulas for all the cells. (Im sure theres a much easier way€¦ <smile) Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S Cell T 0730 1600 8.00 1200 1230 0.50 0900 1300 3.50 07:30 16:00 8.00 12:00 12:30 0.50 0900 1300 3.50 Formula: =IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),(((S14-R14)*24)-J14),IF(AND(R14=H14,S14=I14),((S14-R14)*24)))) Id like the time format to show 3.30 instead of 3.50 and have the *24, without it the answer is -0.33. Help, please? |
If Statement to subtract €śtime€ť if condition met
I dont know whether this post will help you are not. But I am sure that
your most of the data is not in Time format. For example the CellR, Cell J and Cell T values are something like Numberformat & Text format. First make the data in correct format. Apart from that you have not mentioned what data you are having in S14 and R14. Then finally for finding the difference between two different times you are using (S14-R14)*24 I dont know why you should not use the Time format (i.e.) =TEXT((S14-R14),"H:MM") to Convert it to Time format. The below mentioned formula is not perfect, because I dont know what values are you are having in cells S14 & R14 and what you are looking for, But have a look at it. =IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),TEXT(TEXT((S14-R14),"H:MM")*24-J14,"H:MM"),IF(AND(R14=H14,S14=I14),((S14-R14)*24)))) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "BumblebeeFan" wrote: First thanks for all the great info to get me this far! Think Im close, but cannot seem to get the time format to show properly. Help? Trying to subtract €ślunch period€ť if partial day leave includes lunch start/stop times €“ the second row contains the formulas for all the cells. (Im sure theres a much easier way€¦ <smile) Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S Cell T 0730 1600 8.00 1200 1230 0.50 0900 1300 3.50 07:30 16:00 8.00 12:00 12:30 0.50 0900 1300 3.50 Formula: =IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),(((S14-R14)*24)-J14),IF(AND(R14=H14,S14=I14),((S14-R14)*24)))) Id like the time format to show 3.30 instead of 3.50 and have the *24, without it the answer is -0.33. Help, please? |
If Statement to subtract €śtime€ť if condition met
"BumblebeeFan" wrote: First thanks for all the great info to get me this far! Think Im close, but cannot seem to get the time format to show properly. Help? Trying to subtract €ślunch period€ť if partial day leave includes lunch start/stop times €“ the second row contains the formulas for all the cells. (Im sure theres a much easier way€¦ <smile) Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S Cell T 0730 1600 8.00 1200 1230 0.50 0900 1300 3.50 07:30 16:00 8.00 12:00 12:30 0.50 0900 1300 3.50 Formula: =IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),(((S14-R14)*24)-J14),IF(AND(R14=H14,S14=I14),((S14-R14)*24)))) Id like the time format to show 3.30 instead of 3.50 and have the *24, without it the answer is -0.33. Help, please? Hi Bumblebeefan: please use the right cell formatting.. "HH:mm" it will solve your problem. |
If Statement to subtract €śtime€ť if condition met
"BumblebeeFan" wrote: First thanks for all the great info to get me this far! Think Im close, but cannot seem to get the time format to show properly. Help? Trying to subtract €ślunch period€ť if partial day leave includes lunch start/stop times €“ the second row contains the formulas for all the cells. (Im sure theres a much easier way€¦ <smile) Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S Cell T 0730 1600 8.00 1200 1230 0.50 0900 1300 3.50 07:30 16:00 8.00 12:00 12:30 0.50 0900 1300 3.50 Formula: =IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),(((S14-R14)*24)-J14),IF(AND(R14=H14,S14=I14),((S14-R14)*24)))) Id like the time format to show 3.30 instead of 3.50 and have the *24, without it the answer is -0.33. Help, please? |
If Statement to subtract €śtime€ť if condition met
Thank you, your explanation did make me take a second look at the cell
formatting just to make sure. Maybe to help clarify: Row 13 is the first row, which is used to enter data (as text); Row 14 is the second row, which converts the text to time or number format and is used for all the calculations; Columns K through Q are just simple time subtractions, they do not reference other cells for input/data. Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S Cell T 0730 1600 8.00 1200 1230 0.50 0900 1300 3.50 07:30 16:00 8.00 12:00 12:30 0.50 09:00 13:00 3.50 The suggested formula didn't work, but it was certainly more my fault for not "copying" the information properly (R14 & S14 are in time format not text as shown in my original post). Maybe rethinking this would be easier... Is there a way for the user to enter "time" as 0700, 7:00, or 07:00 and Excel treat it all the same? My biggest problem is trying to get all the "what ifs" in place rather than force a "you will enter time this way"... Suggestions? =IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),TEXT(TEXT((S14-R14),"H:MM")*24-J14,"H:MM"),IF(AND(R14=H14,S14=I14),((S14-R14)*24)))) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "BumblebeeFan" wrote: First thanks for all the great info to get me this far! Think Im close, but cannot seem to get the time format to show properly. Help? Trying to subtract €ślunch period€ť if partial day leave includes lunch start/stop times €“ the second row contains the formulas for all the cells. (Im sure theres a much easier way€¦ <smile) Cell E Cell F Cell G Cell H Cell I Cell J Cell R Cell S Cell T 0730 1600 8.00 1200 1230 0.50 0900 1300 3.50 07:30 16:00 8.00 12:00 12:30 0.50 0900 1300 3.50 Formula: =IF(AND(R14="",S14=""),"",IF(AND(R14<=H14,S14=I14 ),(((S14-R14)*24)-J14),IF(AND(R14=H14,S14=I14),((S14-R14)*24)))) Id like the time format to show 3.30 instead of 3.50 and have the *24, without it the answer is -0.33. Help, please? |
If Statement to subtract €śtime€ť if condition met
Got it to work using:
=IF(OR(AND(R14=""),AND(S14="")),"",IF(AND(R14<=H14 ,S14=I14),(((S14-R14)*24)-J14),IF(OR(AND(R14=I14,S14=I14),AND(R14<=H14,S14 <=H14)),((S14-R14)*24)))) and setting format to [h].mm Thanks for your time and help; looking at other posts and trying different things finally worked. |
If Statement to subtract "time" if condition met
You seem to have some unnecessary functions in there.
OR(AND(R14=""),AND(S14="")) is just OR(R14="",S14="") -- David Biddulph "BumblebeeFan" wrote in message ... Got it to work using: =IF(OR(AND(R14=""),AND(S14="")),"",IF(AND(R14<=H14 ,S14=I14),(((S14-R14)*24)-J14),IF(OR(AND(R14=I14,S14=I14),AND(R14<=H14,S14 <=H14)),((S14-R14)*24)))) and setting format to [h].mm Thanks for your time and help; looking at other posts and trying different things finally worked. |
If Statement to subtract "time" if condition met
Thank you! I wasn't sure about the "or/and" and if it mattered -- copied the
formula from another post. Thank you, this will certainly help in the future! "David Biddulph" wrote: You seem to have some unnecessary functions in there. OR(AND(R14=""),AND(S14="")) is just OR(R14="",S14="") -- David Biddulph |
| All times are GMT +1. The time now is 11:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com