Formula not working when criteria cell is populated with a formula

[chriswrcg]

I have a simple logic statement that checks the time and if it is less than a set time it is to display a 1. If the time is greater then display a 2. It works great when I type the times out but when I populate the question cell with Now() formula it no longer works.
How do you get the logic test to look at the value of the cell and not return a "negative" when it sees a formula?

[etaf]

is that because the date is included in now()

Need to see the formula you are using - otherwise guessing
plus

A sample sheet would help here, and possibly get quicker more accurate solution

The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[JohnTopley]

Try

=IF(D2="Saturday",3,IF(D2="Sunday",3,IF(D2="Monday",3,IF(MOD(E2,1)<E3,1,2))))

You are comparing Date+Time [NOW()] rather than Time

[josephteh]

Change E2 to =MOD(NOW(),1) to cut off the date.

[Pete_UK]

You can change your formula to this:

=IF(LEFT(D2)="S",3,IF(D2="Monday",3,IF(MOD(E2,1)<MOD(E3,1),1,2)))

Note that the NOW() function returns the date as well as the time, as Wayne stated, so the MOD function strips out the date part. Strictly speaking, the second MOD function is not necessary, but it is probably safer to leave it in.

Hope this helps.

Pete