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

 

[theBardd]

How about this?

=IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM",""))

 

[markvsmy]

k

How about this?

=IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM",""))

Wow very clever really impressed thank you works perfectly

I have also got just straight 30 and 60 days from invoice date to incorporate in the J column, I guess I could code it 60FI and 30FI but how could I make that work with the code above is that possible?

You might be thinking just stick to 1 set of payment terms, but I run a courier business and these different payment terms are set by other courier companies I sometimes do sub contract work for.

Thanks again
Mark

 

[theBardd]

I guess it would be

=IF(RIGHT(J4,2)="FI",C4+SUBSTITUTE(J4,"FI",""),IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM","")))​

 

[markvsmy]

I guess it would be

=IF(RIGHT(J4,2)="FI",C4+SUBSTITUTE(J4,"FI",""),IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM","")))​

Awesome, thank you so much, going to study this now to understand how it works, works a treat

 

[markvsmy]

Sorry one last question, I have the word "False" where no data is input on the accounts sheet yet, used iserror years ago, and also used conditional formatting to hide it, what would you recommend? Thanks

 

[theBardd]

I would recommend that you decide what should happen if there something other than nnEOM or nnFI in J4, and we build the formula to act accordingly.

 

[markvsmy]

I would recommend that you decide what should happen if there something other than nnEOM or nnFI in J4, and we build the formula to act accordingly.

I would just need it to be blank as its waiting for data entry when the next invoice is produced, I have solved with conditional formatting but its a crude way of fixing it

Thanks

 

[joeu2004]

=IF(J4="", "", 
IF(RIGHT(J4,2)="FI", C4+SUBSTITUTE(J4,"FI",""),
IF(RIGHT(J4,3)="EOM", EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM",""), "ERROR")))

Alternatively, if payment is always due only in 30, 45 or 60 days (or any 2-digit number of days, 10 to 99), and if the suffix is only "FI" or "EOM":

=IF(J4="", "", IF(RIGHT(J4,2)="FI", C4, EOMONTH(C4,0) + LEFT(J4, 2)))

However, according to a wiki page, the term "net 30 end of month" usually means the end of the month on or after 30 days. In other words, EOMONTH(C4+30,0), not EOMONTH(C4,0)+30.

In that case:

=IF(J4="", "", 
IF(RIGHT(J4,2)="FI", C4+SUBSTITUTE(J4,"FI",""),
IF(RIGHT(J4,3)="EOM", EOMONTH(C4+SUBSTITUTE(J4,"EOM",""),0), "ERROR")))

or

=IF(J4="", "", IF(RIGHT(J4,2)="FI", C4+LEFT(J4,2), EOMONTH(C4+LEFT(J4, 2),0)))

Caveat.... SUBSTITUTE is case-sensitive, but the RIGHT(...)=... is not. So "30fi" results in a #VALUE error. If that is an issue, use UPPER(J4) in the SUBSTITUTE expressions.

 

[markvsmy]

Hi thanks, the EOM is paid 30 or 45 days after the last day of the previous month (ie month work was carried out) will try the third version, thank you