CODEWITHSUNDEEP
excelexcel-formula
RickD
RickD

Reputation: 21

IF/AND formula on Excel 2007

I am trying to write a formula to compare a couple of cells and return time(Hours and minutes, not a specific time). Here is the formula that I have come up with:

=IF(AND(C18<"24:00",E18>1),"4:30",G18)

C18 = time that may be less than 24:00 hrs or more than 24:00 hrs (ie: 6:30/32:32) E18 = a number such as 1 or 2

My goal is: if C18 is less than 24:00hrs but E18 is greater than 1 (ie:2), then display "4:30"(TRUE), else display what is in cell G18(FALSE).

I can't get the formula to come back FALSE when I manipulate C18 but I can when I manipulate E18. I've tried several different formula formattings but can't seem to make it work. When I take the "" out from around "24:00", Excel comes back with an error message. Where am I going wrong?

Upvotes: 0

Views: 105

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

Is this what you need ??:

=IF(AND(C18<1,E18>1),TIMEVALUE("4:30"),G18)

since 24 hours corresponds to 1 in Excel.

Upvotes: 1

Related Questions