If statement using date less than number of days

sholmes

Hi, I am trying to write a statement so say if the date in the column is over 10 years from that date, I need to flag it if not then no flag. Any suggestions?

Chemi Pérez

An easy way, adding 3.650 days to that day. And IF(DATE+3650<TODAY(); 1; 0)

Something like this. Take into account that you would have a couple of leap.years, so maybe better add 3.652 days

sholmes

Hi, that is not working. I have a column that has a date that I need to add the 10 years to 3650 days. Any other suggestions?

DKazatsky2

Hi @sholmes,

Are you trying to determine if the date is 10 years before today, 10 years after today, or within 10 years of today in either direction?

Thanks.

sholmes

@DKazatsky2 I am trying to determine if the date is 10 years after the date that is listed in the column. The dates in the column vary by the project, if it has been 10 years since that date we need to know so wanted it flag to make it stand out so the viewers will take the additional step when needed. I also need to know if it's 10 years after the date in the column and if there is a value in another column, this is what I need to trigger the flag.

DKazatsky2

@sholmes,

Give this a try.

=IF(Date@row + 3650 < TODAY(), 1, 0)

Change the semi-colon to coma in the original formula suggested.

Hope this helps,

Dave

sholmes

Thanks @DKazatsky2 but it is still not working

I am using =IF(QQ. Acquisition Closing Date@row + 3650 < TODAY(), 1, 0) b/c the column name is the one that starts with QQ that has the original date that I am trying to determine 10 years after. I get the #unpearsable message.

DKazatsky2

@sholmes

If a column name has spaces, it needs to be inside square brackets - [].

=IF([QQ. Acquisition Closing Date]@row + 3650 < TODAY(), 1, 0)

sholmes

Thanks, all set now. I really appreciate it!

sholmes

Hi @DKazatsky2 , I was wondering if you could help me update this formula. I now need it to flag if

=IF([QQ. Acquisition Closing Date]@row + 3650 < TODAY(), 1, 0) and if the column before it has a dollar amount greater than zero.

DKazatsky2

Hi @sholmes,

Give this a try.

=IF(AND([QQ. Acquisition Closing Date]@row + 3650 < TODAY(), [column name]@row > 0), 1, 0)

Hope this helps,

Dave

sholmes

Awesome, thanks

sholmes

Hi @DKazatsky2, I was wondering if you could help me with another issue. Somone before me was using this formula:

=IF([Est 2024 Bonus Depreciation (FM)]@row + [2023 Bonus Depr. (AM) 4562, line 14]@row = 0, "OK", IF(ISBLANK([2023 Bonus Depr. (AM) 4562, line 14]@row), "OK", IF(ABS([Est 2024 Bonus Depreciation (FM)]@row - [2023 Bonus Depr. (AM) 4562, line 14]@row) / ([2023 Bonus Depr. (AM) 4562, line 14]@row) < ,0.1 "OK", "Alert")))

It's not working, what I need to do is if [2023 Bonus Depr. (AM) 4562, line 14} is $100.000 or more than what is in [Est 2024 Bonus Depreciation (FM)] then I need it to say Alert otherwise it can say Ok.

Thanks in advance!

DKazatsky2

Hi @sholmes,

Here you go.

=IF([2023 Bonus Depr. (AM) 4562, line 14]@row - [Est 2024 Bonus Depreciation (FM)]@row >= 100,"Alert","Ok")

Hope this helps,

Dave

sholmes

@DKazatsky2

Oops, I typed it wrong, the amount is 100,000. I will give it a try using the correct amount. Does this mean if I wanted to know if it was 100,000 more or negative I could just add the minus sign? Thanks for your assistance.

DKazatsky2

@sholmes,

Yes, I believe that would work (untested).