Calculate Invoice Due Date from VLOOKUP drop down list

[Jigby]

Hi,
I am trying to produce a basic spreadsheet for accounts that calculates payment due dates of invoices. We have different payment terms for Suppliers (30 days from month end), Sub Contractors (45 days from month end) and Labour Only Sub Contractors (21 days from date of invoice). I have created a drop down list for Suppliers, Sub Contractors and Labour Only's and I have utilised the VLOOKUP function to pull the payment dates through into my spreadsheet. Trouble is I cannot seem to work out the due date given that each item on the list has a different payment term. It's almost as though I need to VLOOKUP a formula from the table rather than a value.

Any ideas?

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Jigby]

Thanks AliGW.

I have attached the spreadsheet below.

So Column A has a drop down menu of suppliers, subcontractors and labour onlys.

I want the due date column to automatically produce the payment due date based on the parameters below.

Suppliers - Due Date is EOMONTH invoice is dated + 30 days
Subcontractors - Due Date is EOMONTH invoice is dated + 45 days
Labour Only's - Due Date is 21 days after invoice is dated

[Glenn Kennedy]

Try this:

=IF(A4="Supplier",EOMONTH(D4+30,0),IF(A4="Sub-Contractor",EOMONTH(D4+45,0),IF(A4="Labour Only",D4+21,"")))

[AliGW]

Like this:

=IF(A4="Labour Only",D4+E4,EOMONTH(D4,0)+E4)

[Jigby]

Wow, that's great guys! The Sub-Contractor, despite having 45 days in the formula still spits out as if it was 30 days?

[AliGW]

Use my formula instead of Glenn's.

See post #5.

[Jigby]

Brilliant. That has saved me so much time. No doubt I will be back with more questions in the future.

Thanks once again.

[AliGW]

You're welcome! See you again.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Richard Buttrey]

Wow, that's great guys! The Sub-Contractor, despite having 45 days in the formula still spits out as if it was 30 days?

Despite your comment in 8 I doubt this is yet correct unless I'm missing something vital.

There is a subtle but important difference between

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

With the Subcontractors date of 12/4/2017 the former results in 31/5/2017 whilst the latter, which I believe is what you want given #3 and you DO mean 45 days after the Month end date of the Invoice date is 14/6/2017.
And with the Suppliers requirement of Invoice Date + 30 then that should be 30/5/2017 and not 31/5/2017. Hence I believe the whole formula should be.

Please Login or Register  to view this content.

The original default False function for Labour only was also redundant.

[AliGW]

Richard - I have used this:

=EOMONTH(D1,0)+30

because the OP stated:

Suppliers - Due Date is EOMONTH invoice is dated + 30 days
Subcontractors - Due Date is EOMONTH invoice is dated + 45 days
Labour Only's - Due Date is 21 days after invoice is dated

This to me means the end of the month of the invoice date (so invoice any time in April would give 30/04/2017) plus 30 days (or 45 in the case of sub-contractors).

This:

=EOMONTH(D1+30,0)

is entirely different, as it would first add 30 days to the invoice date before calculating the end of the month. In practice, the difference might seem subtle, but it's critical to the formula being correct. As the OP has stated, the formula I offered is producing the expected results, so we must assume, surely, that I have interpreted the requirements correctly.

Or am I missing your point?

[Richard Buttrey]

Hello Ali,

I agree that the EOMONTH(D1,0)+30 is correct, which is what is contained in the formula I showed.

I was picking up on the OPs statement that for Subcontractors the rule is

Subcontractors - Due Date is EOMONTH invoice is dated + 45 days

and Glenn's formula in #4.

Now to me that means the due date is calculated by adding 45 days to the month end in which the Invoice is dated. And logically it follows that any date in the month will produce the same due date. i.e. 1st April and 29 April will both produce the same due date of 14 June.

The same situation applies to Suppliers but of course the 30 days means that the due date will always be the 30th of the following month

I read your correction #5 (which was only commenting on the 'Labour Only') as implying that you too had the Subcontractors as EOMONTH(D4+45,0)

I was somewhat perplexed by the OPs #6 where he identified that there was still a problem and then in #8 seemed to agree everything was now correct after he'd presumably added the Labour only formula you gave - which apart from a reference to E4 rather than 21 was the same as Glenn's for that particular element.

The top and bottom is that I believe the Subbies due date is 14th June and not 31 May.
Maybe the OP will confirm?

[AliGW]

I agree, and that is what my formula arrives at:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K
3	Invoice Type	Invoice Name	Invoice Number	Invoice Date	Payment Terms (days)	Sub Total	CIS Total	VAT Total	Total	Amount Payable	Due Date
4	Sub-Contractor			12/04/2017	45					£ -	14/06/2017
5	Labour Only			12/04/2017	21					£ -	03/05/2017
6	Supplier			12/04/2017	30					£ -	30/05/2017
7	Direct Debit				0					£ -

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	K
4	=IF(A4="Labour Only",D4+E4,EOMONTH(D4,0)+E4)

Sheet: Sheet1

[Richard Buttrey]

...nevertheless I'm still perplexed.

At the time the OP said all was well in #9, as far as I can see all they had at that time was Glenn's formula (which gives the wrong date for the Subbies) and your bit of the formula from #5 which only mentioned the Labour Only bit.

So at that point as far as I can see the Subbies date is wrong but the OP seems to be happy.