# Excel dates Calculation

0

Good day

I urgently need help with a formula for dates, when I have a cutoff date (20171130) the invoice date the user enters should populate a "X" in either the 30-day column or a "X" in the 30DayPlus-column.

The formula that I am using is

=IF(ISBLANK(A3)," ",IF(A3>D3,"X"," "))    for the 30 day column

and for the 30DayPlus column I am using

=IF(ISBLANK(A3)," ",IF(A3<=D3,"X"," "))

This is not working correctly because when my cuttoff date is 20171130 and my invoice date is 20171101 the "X" should be in the 30Day column.

I hope this make sense. It is for a accruals list that I need this(see below). The bold lines are then incorrect.

Accruals Cutoff: 2017-11-30   2017-11-30 Invoice Date 30 Days  (mark with X) 30 Days +  (mark with X) 2017-11-30 2017-09-30   X 2017-11-30 2017-09-02   X 2017-11-30 2016-10-10   X 2017-11-30 2017-11-05 X 2017-11-30 2016-11-30   X 2017-11-30 2017-09-20   X 2017-11-30 2017-09-29   X 2017-11-30 2017-10-01   X 2017-11-30 2017-11-01 X 2017-11-30 2017-12-01 X   2017-11-30
``Code_Goes_Here``

0

In principle, you forgot to include the 30 days in your formula. These formulas should work instead of the ones you have.

``=IF(ISBLANK(A13)," ",IF(A13>D13-30,"X"," "))``

and

``=IF(ISBLANK(A13)," ",IF(A13<=D13-30,"X"," "))``

However, I would recommend that you don't enter a space in the blank cells. Blank spaces always cause problems and never did anything good. Use "" (without space) instead of " ".

The other suggestion I have is to replace the nested IF with a logical concatenation. This, as well as the above, is incorporated in these two formulas.

``=IF(AND(NOT(ISBLANK(A3)),A3>D3-30),"X","")``
and
``=IF(AND(NOT(ISBLANK(A3)),A3<=D3-30),"X","")``
I hope this works for you.

### Discussion

Hello Variatus
Thank you very very much. It seems so simple now.
Kind regards

Merinda
Lola Dec 13, '17 at 4:21 am
Yes, it usually does. I'm glad I could help. Please accept the answer, as a beacon to others and reward to me. Thank you.
Variatus (rep: 3313) Dec 13, '17 at 4:30 am
Hello Variatus
This works 100% except when you change to a new year. The cut-off-date is 2017/11/30 but when I enter invoice date 2018/01/01 it says its in the 30 days which it is not. How do I add 1 to the year?
Lola Dec 13, '17 at 7:28 am