I have a sheet with events/descriptions and the specific time the event begins. I am trying to get the event description for the current time (NOW). I have tried a function =INDEX(K2:K,MATCH(NOW()-TODAY(),L2:L,1)) But it is not working.
The sheet itself has a lot of info in it, but the section where I am trying to figure this out is basically L:Q
I’ve attached an image, markdown table and sheet link.
Thank you for your time and input!
| Return Just Todays Date | Location | Description | Event’s Time | How long is event? | CURRENT EVENT | How long is event? |
|---|---|---|---|---|---|---|
| 12/8/2023 | Jill’s office | Outline for Ford presentation | 9:00:AM | 1:15 | #N/A | |
| 12/8/2023 | Room C | Get notes back to Sam | 10:45:AM | 2:15 | ||
| 12/8/2023 | Harrison’s Cafe | Review site updates | 1:00:PM | 1:30 | ||
| 12/8/2023 | Soulo’s Kitchen +Cafe, 635 N Broadway, Los Angeles, CA 90012, USA | Ask about Sara | 6:30:PM | 3:00 |
Try this out:
=ARRAYFORMULA(
LET(event, C2:C,
event_time, --REGEXREPLACE(D2:D,":([AP])","$1"),
event_duration, E2:E,
current_time, NOW() - TODAY(),
FILTER(event, (current_time >= event_time) *
(current_time <= event_time + event_duration))))
To get most recent event use
SORT()function as descending order then grab only first one. You can do it bySORT()thenINDEX()or usingQUERY().your sheet is private by the way.