Forum Discussion
Excel Formula Assistance
I am trying to use a formula within an existing formula so that when the date in a cell reaches a year or greater, the cell value becomes "0". Cell A4 contains the date, Cell E is where I need the value to be 0 if the date has met or exceeded 1 year.
This is what I have so far; the results in E4 do not return as "#NAME?"
=IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(YEAR(TODAY())-YEAR(A4)-365,"0"),0,"")))
Here is what I'm looking at:
If it helps, I'm utilizing Microsoft Edge for Microsoft 365 Excel
=IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(YEAR(TODAY())-YEAR(A4)-365,"0"),0,"")))
Does it work if you add () after TODAY? In the formula the additional () are in red. If i use this formula and enter "Absent" in cell B4 then the formula returns 1. However i'm not sure what IF(OR(YEAR(TODAY())... should return.
I guess to put it a little simpler:
Once the date in Column A is equal to or greater than 1 year, I need the value in Column E to be "0" regardless of the information in Column B.=IF(TODAY()-A4>=365,0,IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,"")))
Do you want to return 0 if the date in A4 is at least 365 days in the past (calculated from today)? The formula checks if the date in A4 is at least 365 days in the past regardless of the information in cell B4. If the date in A4 isn't at least 365 days in the past the formula checks cell B4.
