IFAND function not working

[Tristram_ZX81]

Hello. I'm trying to get excel in Office 365 to count a freelancer rate depending on who it is and what time they started. So in the example below, you'd want it to say 100 if Matt started at 19:00, but 80 if he started at 10:00. However as it stands it always counts the lower, pre 15:00 figure for everyone. Any ideas?

=IF(AND(A613="Matt",E613>"14:59"),100,
IF(AND(A613="Matt",E613<"14:59"),80,
IF(AND(A613="Alex",E613>"14:59"),120,
IF(AND(A613="Alex",E613<"14:59"),100,
IF(AND(A613="Harry",E613>"14:59"),200,
IF(AND(A613="Harry",E613<"14:59"),150,
))))))

 

[RoryA]

"14:59" is text, not a time. Use TIME(15,0,0) instead. I'd suggest putting it in a cell or defined name so you don't have to keep typing it, or use LET in your formula.

 

[Peter_SSs]

Also, instead of continually repeating the time, perhaps a structure like this could be used?

=INDEX(IF(E613>=TIME(15,0,0),{100,120,200},{80,100,150}),MATCH(A613,{"Matt","Alex","Harry"},0))