Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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
Answer
Discuss

Answers

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

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: 4889) 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
The formula also does not work when the invoice date is 2017/10/31. Please help.
Lola Dec 13, '17 at 8:17 am
The formula works correctly in both cases, but my understanding of your need might be wrong. In the first case my presumption is that you want to know how long an invoice is overdue at the cut-off date. An invoice issued after the cut-off date doesn't fit "30 days" or "30 days plus". Therefore you would need to specify what to do in such cases, perhaps ignore or create a third column. However, if your list is of overdue invoices none of them would have a future date.
Could it be that you confuse "30 days" with "one month" in the second case? 2017/10/31 is 31 days before 2017/11/30. If you want a one-month rule the formula would have to be tweaked. It isn't difficult but it makes a lot of difference in February.
Variatus (rep: 4889) Dec 13, '17 at 8:55 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login