Forum Discussion

NeomaRice's avatar
NeomaRice
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
NeomaRice's avatar
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's avatar
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's avatar
    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!!

Resources