Need help with a formula to pick the nearest Date.

[jen33pop]

I am using Excel 2007. Here is what I'm trying to do.. I work for a company that takes monthly EFT payments. Payments are pulled only on certain dates, and each customer's due date is based on the effective date of their account. So what I'm trying to do is to provide the customer with a year long payment schedule so they know when the payment will be pulled from their account. I have listed the EFT pull dates from H4-H87.

I need one formula to give me a year's worth dates that their payments are due (12 times per year). The kicker with this one is that I need to factor in that accounts whose effective dates are on the last day of the month, may have a due date on the first of the following month. For example, someone with an effective date of January 31, would have a payment date of March 1 for their February payment since February only has 28 or 29 days.

The second formula needs to be able to take each monthly due date and find the closest date after that due date from the list of dates that we are going to pull ach.

The idea is that I can give the customer a copy of exactly when they can expect us to pull from their bank accounts based on the effective date of their account.

Thanks ahead of time for your help!

 

[jasonb75]

For example, someone with an effective date of January 31, would have a payment date of March 1 for their February payment since February only has 28 or 29 days.

Welcome to the board!

What about other months? April 30 or May 1? What is the criteria for calculating the correct date? Does a due date that falls in a weekend make any difference?

 

[jen33pop]

Welcome to the board!

What about other months? April 30 or May 1? What is the criteria for calculating the correct date? Does a due date that falls in a weekend make any difference?

If the effective date was Jan 31, the April due date would be May 1.

The ach pull dates are already listed. I did that manually. No need to worry about weekend dates or holidays. That is already factored in. What I'm trying to achieve is two-fold.

1. Use a formula to calculate the due dates based on the effective dates.
2. Use a formula to find the nearest pull date that is either on or shortly AFTER the due date.

 

[jasonb75]

Ok, see if this produces the correct dates.

In blank sheet, enter an effective date in A1, then enter this formula in A2 and use the fill handle to copy it down.

=EDATE($A$1,ROWS(A$2:A2))+(DAY(A$1)>DAY(EDATE(A$1,ROWS(A$2:A2))))

 

[jen33pop]

What shows up is 42064. Not sure what to do with that.

 

[jasonb75]

Sorry, I should have said, for some reason excel shows the date serial numbers instead of real dates.

If you format the cells as date then they will appear as normal.

 

[jen33pop]

That worked! OMG I've been working on this all morning and you've just figured it out in no time flat!! Thanks so much!

I'm not sure about the second formula now..I've been told Vlookup, index...and I'm not quite sure which of those would suit my situation better.

 

[jasonb75]

The easiest way would be index and match, but I'm going to guess that your list is in ascending order. As you want the next date after, your list will need to be in descending order.

If my guess is correct then (assuming that column I is empty), enter this formula in I4

=INDEX($H$4:$H$87,ROWS($H4:$H87))

Then use autofill to copy that all the way down to I87.

Next copy I4:I87, then select H4, right click and pastespecial, change the default selection of 'All' to 'Values' before clicking ok. Now your list will be in the reverse order.

Now you should be able to use

=INDEX($H$4:$H$87,MATCH(A1,$H$4:$H$87,-1))

with the due date in A1 to get the pull date.

Hopefully that all makes sense.

I would also suggest to double check the dates in your list, if you entered only the day and month when you made your list, excel would default the year to 2015, which would give wrong results if you were passing dates in 2016.

 

[jen33pop]

Yes!! It works!! I was able to sort my dates in descending order. Then I used the formula and it worked like a charm! I have the year included in the dates, so that won't be a problem. You have no idea how much easier this is going to make my job! Thanks so much!!!

 

[jasonb75]

I have absolutely no idea why I suggested such a long winded method for a simple sort, guess I must have switched brain off ready for the weekend lol.