On B2, I'm trying to put in an IF timevalue statement on a datetimevalue statement with this formula

=IF (A2 < TIME (7,30,0), "PREM", IF (A2 >= TIME (7,30,0), "INTRA", "0"))

However it doesn't come out as I planned. It kept showing all of column B to be INTRA instead. How can I convert it so that it looks like the graph below?

|    TIME IN       | INTRA/PREM  |
| 2/5/2020 7:09:37 | PREM        |
| 2/5/2020 8:01:05 | INTRA       |

I don't know if I explained it correctly, but I want column B to calculate the datetimevalue of column A to either be "PREM" if the time is before 7:30AM, or "INTRA" if the time is at 7:30AM or after 7:30AM.


1 Answer 1


You need the IFS and TIMEVALUE functions as well

=IFS (TIMEVALUE(A2) >= TIME (7,30,0), "INTRA",TIMEVALUE(A2) < TIME (7,30,0), "PREM")

You could even use an Arrayformula

=ArrayFormula(IFS (TIMEVALUE(A2:A3) >= TIME (7,30,0), "INTRA",TIMEVALUE(A2:A3) < TIME (7,30,0), "PREM"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.