EOM formula

[markvsmy]

Hello,

I am sorting my accounting spreadsheet and using the formula IF(J4="30EOM",EOMONTH(C4,0)+30) which works, the problem I have is adding more If variables for this file, most customers are 30 days EOM, but some are 45 EOM, some are just 30 days, some are 60 days. If I can get the first IF variable to work I am sure can then add the rest.

For info I will use a drop down menu to input which of the payment terms applies, C4 is the date, J4 is the payment terms

[TABLE="width: 766"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Customer[/TD]
[TD]Invoice[/TD]
[TD]Amount[/TD]
[TD]VAT[/TD]
[TD]VAT[/TD]
[TD]Total[/TD]
[TD]Terms[/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD]01/04/17[/TD]
[TD]XXX Logistics[/TD]
[TD]285[/TD]
[TD]£1,020.00[/TD]
[TD]Y[/TD]
[TD]£204.00[/TD]
[TD]£1,224.00[/TD]
[TD]30EOM[/TD]
[TD]30/05/17[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for help,

Kind regards
Mark

 

[joeu2004]

Hmm, by my count, the "third version" has the expression EOMONTH(C4+SUBSTITUTE(J4,"EOM",""),0). That is not "30 ... days after the last day of the previous month".

For the latter, I think "theBardd's" expression is correct, to wit: EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM","").