How to use the current time & date to display specific value?

Question

I'm trying to set up an excel sheet which displays the name of the person available at the current time and date. I have one sheet with a list of dates and times along with the persons name who is available at that time. For example:

- 01/07/19 09:00 Bob
- 01/07/19 10:00 Bill
- 01/07/19 11:00 Ben

I can do this successfully for a full day or week using the WEEKNUM or DAYNUM functions, and the week/day number listed against the list of names.

I can use NOW() to get the current date and time, but I'm unsure how I can use this to get the desired results.

I've tried searching online for the answer but have been unable to find what I'm looking for.

Based on my example above, if the current time is between 9:00 & 10:00 I want the name Bill to be displayed.

Answer 1

Here is one way:

Formula in F1:

=INDEX(C2:C4,MATCH(1,INDEX((A2:A4=TODAY())*(HOUR(B2:B4)<=HOUR(NOW()))*(HOUR(B2:B4)>=HOUR(NOW())),0,1),0))

What's happening? We use MATCH to get the first TRUE in an INDEX list that compares column A against TODAY times column B for HOUR.

We can leave out the second INDEX but that would make it an array formula to be entered through CtrlShiftEnter

Note that this example is made around 9:30 on the 4th of July.

Answer 2

Based on your dates being actual dates stored as number for excel and not numbers stored as text, and same for time, then the following solution should work for you. You can test your data to see if its an actual date using:

=ISNUMBER(A2) `where A2 is the cell in question

If the formula returns true then you know your are dealing with a number that has been formatted to display as a date or time.

As a side note, dates are stored as integers and are the number of days since 1900/01/01 with that date being 1. Time is stored as a decimal representing the portion of the day. 0.5 would be 12 noon.

This solution assumes the data is layed out as per the image below.

In cell F1, place your NOW() function. I have manually entered the date to control the value for demonstration purposes.

In cell F2 enter the following formula:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$2:$A$4)/((INT($F$1)=$A$2:$A$4)*(MOD($F$1,1)<=$B$2:$B$4)),1))

AGGREGATE will make a list or all row number and sort them from largest to smallest. All Those row numbers will be divided by your criteria checks. If your criteria results are ALL True, then the row is divided by 1 and remains unchanged. If a criteria is not met the Row number is divided by 0 which causes an error. The 6 in the function tells aggregate to ignore error results. So you wind up with a list or row numbers that march your criteria. The 15 in aggregate tells aggregate to sort the row numbers from largest to smallest. The 1 tells aggregate to return the row number in position 1 of the sorted list. Aggregate then passes that row number to INDEX which finds the row in the corresponding column and returns the address of that cell.

now just so you are aware. The break point for time was set so that the person beside the list time is the one that will be picked when that exact time is provided. Meaning for a time of 0900 on the specified day, Bob would be selected. At 10:00:01 Ben will be selected. At 11:00:01 an error will be generated. you can control this by placing the whole thing in an IFERROR function. At 00:00:00 Bob is selected.

Answer 3

I believe this will do what you want:

=INDEX(C2:C4,MATCH(NOW()-TODAY(),B2:B4,1))

Where column C contains the names and column B the times

If date and time are in one column, you can try:

=INDEX(B1:B3,MATCH(NOW(),A1:A3,1))

Where column B contains the names and column A the date and time