Forum Discussion

Copper Contributor

Apr 17, 2024

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 va...

excel

Excel for web

Formulas and Functions

OliverScheurich

Gold Contributor

Apr 17, 2024

NeomaRice

=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.

NeomaRice
Copper Contributor
Apr 17, 2024
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.
- OliverScheurich
  Gold Contributor
  Apr 17, 2024
  NeomaRice
  =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.
  - NeomaRice
    Copper Contributor
    Apr 17, 2024
    YES, thank you!
    
    However, now E4 has "0" in the rows where there is no data when previously, the formula results were not visible.
    
    EDIT: Scratch that, I fixed it! Just had to substitute "" instead of 0 for the value 🙂
    
    THANKS SO MUCH!!