If then formula question [Closed]

Report
-
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016
-
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 replies


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

Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016

My spreadsheet has both those cells formatted as [h]:mm, would that be why I'm getting a diffrerent answer than you?
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016

I do need it to be in this format as it's a payroll spreadsheet
Blocked Profile
I have not formatted the sheet. When I change the time format, it still doesn't break.
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016

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.



See, I have changed the time format.
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016

What happens if you format d14 as [h]:mm?
Blocked Profile >
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016

let me try, standby!
Blocked Profile
It seems the formula returns 44 regardless of value! Let me look into some different things. Standby

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.
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016

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.
Blocked Profile
Can you insert another column and calculate another serialized representation of the "hours" worked, then evaluate that?

Sorry for the delays in my response.
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016
> Blocked Profile
Sorry, I had to leave. I just tried that and it didn't work either.
Blocked Profile
well, the extra column should work, as you are converting the time to serialized number.
Posts
7
Registration date
Friday September 9, 2016
Status
Member
Last seen
September 13, 2016
> Blocked Profile
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!