Formula for highlighting a cell when time equals NOW

Hello again guys, first of all I would like to thank you in advance for the help.

Lately, I have been trying to improve the formula for highlighting a cell when time= NOW, which has been very helpful. However, since a lot of people is coming to the company, I am having difficulties with adding everyone's information because I have to do it manually. Basically, the rule I am following is "show the status a person should be (available, break, lunch, off) when time=NOW ()" in other words, IF "T3:Z64" of 'Schedules!'= Now() THEN apply the custom formula. I'm sorry if I am not being clear. Please don't hesitate to ask. See the attached file for references.

Thanks in advance,

Thank you for your answer Jeff, it is much appreciated. I opened the file, and it highlights cells with times that have even passed already.

Here is the formula I am trying to use with my principle =AND('cell name'<>"OFF",MOD(NOW(),1)>='cell name',MOD(NOW(),1)<='time value in numbers')

Basically the condition is when the value of the text is not equal to 'off'apply the mod of now().

So, what I am trying to do is instead of putting a cell name to the formula, I would like the formula to refer to the whole sheet.
For example, when it's 6 AM, employee 1 has to be available and show green on the Diagram 1 sheet.

I've been trying with an IF function (IF([Diagram Scheme Practice]Schedules'T3:Z64!") to refer to the condition, but it does not work.

Here's an alternative method that works, output in AI:AJ. The table in AL:AM is there to convert the status from column R into something more meaningful.

Note that AI1 does not contain a NOW() formula, it contains a formula that gives random days from the dates in column S and random times between 05:00 and 17:00 at 5 minute intervals. I've done this so that you can test it easier by recalculating the sheet with f9.

Thanks for the help Jason, I have reviewed the file, and thanks for the help. I was looking at diagram, but I do not see the status the employees should be, I applied your formula as a conditional formatting too but still nothing happens.

There is nothing in the diagram sheet, that can be deleted, it is all done in the other one.

That's why I said it was an alternative, because it's not exactly what you asked for, but a simplified alternative.

Thanks for the response Jason, this definitely helps me with my goal. I will check it out and will mark as solved if it works. Thanks again Jason and Jeff.

You're welcome!

Just a quick reminder that you will need to change 1 thing before it will work in real time. The formula in AI1 is creating random times for testing, you will need to replace it with =NOW() for it to work with the current time.

Also, as Jeff pointed out earlier, you will need to refresh the sheet with f9 for =NOW() to update, it is not a constant refresh.

So I changed the AI1 formula from =RANDBETWEEN(S3,S3+6)+(1/288)*RANDBETWEEN(60,204) to =NOW(). Unfortunately, it is not working anymore. When I was using the randomizer it worked perfectly, but with the now formula, it doesn't. Additionally, I used the TODAY() function also. Thanks in advance.

What would you expect now that you have entered the NOW() function?

There is no data for today in column S, so the results will be blank.

After 05:00 tomorrow morning, Agent 6 will show as available, (date in S3 + time in T3), with the rest showing up at the scheduled times.

Thanks for responding. I'm sorry if I ask too much questions, I am new at using excel for workforce management. I honestly don't understand how the NOW() formula can apply to the data on S so that I get the status on real time.

If you look at the formula in AJ3, that is the one that does all of the work, it just uses the NOW() function as a reference point for the current time.

This is the key part of the formula in AJ3 (for Agent 1)

($S$3:$S$64=INT($AI$1))/(INDEX($T$3:$Z$64,0,MATCH($AI3,$T$2:$Z$2,0))<=MOD($AI$1,1))

The part in red isolates the rows in column S where the date matches the NOW() function.
The other part isolates the times in the column for the agent that are earlier than NOW()
INT($AI$1) and MOD($AI$1,1) separate NOW() into date and time respectively so that each can be compared to the relevant parts of the schedule.
Once the above is done, the AGGREGATE function isolates the last of the times that have already been filtered out as earlier than NOW().
This then passes the ROW back to INDEX to get the status from column R
VLOOKUP then passes this through the table in AL:AM to get the meaningful status.
This is the status that is used by the conditional formatting to highlight availability of the agent.

Hope that makes sense.

Thank you for the explanation, so since everything has to match the AI$1$. Then it would be okay if I add the NOW() function on the AI1 cell, right?

Yes, that is correct. Then, tomorrow morning, after 05:00 the statuses will start updating when you hit f9.