Mark an item as arriving late (a little late / a lot late)

Question

I have a spreadsheet that intakes materials from a form that users fill out. Once a day I receive a daily newsletter, and I'd like to track whether that item is received on time (by 11:30 am), late (by noon) or very late (after noon).  I have one column where I personally can check off if the item received is the newsletter, and I'd like the next column for "status" to automatically populate "on time / late / very late" based on the time of the submission form.

I'm pretty new at formulas, and I am still grasping how to deal with indications of time.

I have the basic IF ([newsletter button] = "true") AND (time is XXX), "on time") etc. figured out, but how can I fill in the time based on the "Date of request" field? And can you also help with the specific order of the IF / AND (3 options for AND) phrasing?

Much appreciated.

harryjohn · Accepted Answer

=IF([@Newsletter]="TRUE", IF(TIMEVALUE([@[Date of request]])<=TIME(11,30,0),"On time", IF(TIMEVALUE([@[Date of request]])<=TIME(12,0,0),"Late","Very late")), "")

This checks if the newsletter is marked as received, then looks at the time from the "Date of request" to decide the status. Let me know if your time is in a different format—happy to help adjust!

Danica H. · Accepted Answer

Thank you. I ended up creating a time "helper" column, which pulls the time from the "created" date. Then I can use the IF function based only on that column. Thanks for your help!