Formula to give 1 Point for Called In, .5 Points Late or Leave Early &0 if point year old

The purpose for my formula is to keep a point system for attendance.
If an employee is Late of Left Early they get a .5 Points
If an employee Called In they get 1 Points

There is also a condition that if the Point in that column is over a year old the value should be 0.

I have the formula to get anything older than a year to be labelled 0 and give a false.

=IF(OR((E4>NOW()+365),(E4<NOW())),0)

The problem I am having is adding in the .5 and .1 values when it looks at Cell B.

Any help would be greatly appreciated.

Hi, and welcome to the forum

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

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

First please take the time to review our rules. There aren't many, and they are all important. Rule #1 asks for good thread titles. Your original title was very generic. I have updated it to something a little more specific.

There are a couple of holes in your description. First you referred to 1 point then later to .1 points. Should the first reference be .1 point?

What is in cell E4? You mention "cell B" but what is in it?

Where does it indicate that the employee arrived late or left early?

The formula you showed will return 0 if E4 is more than a year in the future, or is any time in the past. As a consequence, if E4 is anytime from today until a year from now, it will return FALSE. It is unusual to deliberately omit the "false" result and let it default to FALSE. I don't know if the logic is what you intended since it doesn't match your description, and it is unusual return either 0 or FALSE.

As is often the case, it would be very helpful if you provide your file. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window. The submit your post.

Hi,

As Jeff has said it helps us to help you if you upload the workbook so that we can see the request in context.

It appears from your description that you want to be able to select something in column B cells and then have them changed if certain conditions apply. You can't have both a formula in a cell and be able to use a drop down or manually change the cell. You'll need a macro for this unless you perhaps make use of an additional helper column. However upload the workbook so we know what you're working with.