Formula for status update based on date

I have a spreadsheet that shows work tasks that need to be accomplished. Columns A, B, C, and D are as follows: "Task Status", "Date", "Task Details", and "Task Completed?". My boss would like to make the "Status" column update automaticaly. Ideally I would like the spreadsheet to work by using the date. When the user writes down the task they also put an estimated completion date in the "Date" column. If the current date is before the date listed in the "Date" column, the "Status" column will be yellow. If the current date is after the date listed in the "Date" column, the "status" column will be red. If the task is completed, the user marks that in the "Task Completed?" column and the "Status" column becomes green.

To do this I figured I would be able to use conditional formating by using the following formula:

=IF(B3-TODAY()>1,6,4)

So if the date entered in the "Date" column is 19 DEC 2018, the formula will put a 4 in the "Status" column because its currently the 21st and that would mean that the task is past due. In the "Status" column I used conditional formating so any number populated in that field that is lower than 5 will produce the red circle with an x in it.

Next I tried to use a simple "IF" statement that made it to where when you put something in the "Task Completed?" column it would populate a 10 in the "Status" block. This is the formula I tried to use:

=IF(B3-TODAY()>1,6,4,IF(G3="X",10,))

I keep getting "You've entered too many arguments for this function"

Does anyone have any suggestions on how I could possibly accomplish my goal here? I am by no means an Excel guru and would love to learn anything you could teach me. Thanks!

-Steven

Welcome to the forum!

Please note for future reference that your thread title is not sufficient to satisfy this forum's rules and should be changed if you require further help in this thread. (EDIT: I have changed it for you this time).

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.