Previous Topic Next Topic

If then formula question

Closed
T - Sep 9, 2016 at 05:03 PM
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016 - Sep 13, 2016 at 10:36 AM
Hello,
If you have a value in cell a that equal 42:15 (42 hrs and 15 min) and in another cell you want it to look at that cell, determine if the hours are less than 44, and if they are enter 44 hours in that cell. I know this requires an if then, which I tried (=IF(AT32<44,44,0)), but it's returning 1056:00 instead of 44:00. I also tried =IF(AT32<44:00,44:00,0)) but it didn't like that either. Can anyone help me?


3 responses

Answer 1 / 3
Blocked Profile
Sep 9, 2016 at 05:08 PM
Well, it sounds like your cells are formatted incorrectly. I just opened up a new worksheet and recreated your formula, and I get 44! See below
0
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016
Sep 9, 2016 at 05:13 PM
My spreadsheet has both those cells formatted as [h]:mm, would that be why I'm getting a diffrerent answer than you?
0
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016
Sep 9, 2016 at 05:14 PM
I do need it to be in this format as it's a payroll spreadsheet
0
Blocked Profile
Sep 9, 2016 at 05:14 PM
I have not formatted the sheet. When I change the time format, it still doesn't break.
0
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016
Sep 9, 2016 at 05:19 PM
That's odd, because i have =IF(AT32<44,44,0) in cell au32
Cell AT:32=42:15 and the above formula returns 1056:00. Both cells are formatted as I said before. I'm stumped as to why it works for you but not me.
0
Answer 2 / 3
Blocked Profile
Sep 9, 2016 at 05:16 PM


See, I have changed the time format.
0
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016
Sep 9, 2016 at 05:21 PM
What happens if you format d14 as [h]:mm?
0
Blocked Profile > tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016
Sep 9, 2016 at 05:22 PM
let me try, standby!
0
Blocked Profile
Sep 9, 2016 at 05:26 PM
It seems the formula returns 44 regardless of value! Let me look into some different things. Standby
0
Answer 3 / 3
Blocked Profile
Sep 9, 2016 at 05:21 PM
Open up a new worksheet, and try your formula into a new sheet. See if it acts the same. If it does, then it is your local region variables that are causing this.
0
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016
Sep 9, 2016 at 05:24 PM
It changes my 42:15 to 18:15, (however my 42:15, is daily hours adding up to that for the week) and it changes my result to 0:00, not 44:00.
0
Blocked Profile
Sep 9, 2016 at 06:45 PM
Can you insert another column and calculate another serialized representation of the "hours" worked, then evaluate that?

Sorry for the delays in my response.
0
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016 > Blocked Profile
Sep 12, 2016 at 09:40 AM
Sorry, I had to leave. I just tried that and it didn't work either.
0
Blocked Profile
Sep 12, 2016 at 04:56 PM
well, the extra column should work, as you are converting the time to serialized number.
0
tderby Posts 7 Registration date Friday September 9, 2016 Status Member Last seen September 13, 2016 > Blocked Profile
Sep 13, 2016 at 10:36 AM
I tried it again and it didn't work, so I changed the formula to =IF(AT32<44,44/24,0) and now it returns 44:00. Thanks for you help.
0

Similar discussions

Keyboard troubles
jimbobjoe -
car -

38 responses
My account locked connect to gemail
Karthik -
winko -

2 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response