The Book-keepers Forum (BKF)

Post Info TOPIC: Excel help


Master Book-keeper

Status: Offline
Posts: 8646
Date:
Excel help


Hi

Any ideas how I can setup a formula in excel to create a 'last payment date' from a start date. I have eg customer making payments to a client on a lease agreement for 24 months, so need to set up recurring payments, all have differing direct debit payment dates

 

So far I have

Col a                    Col b                  Col c

Start date             months               last date

11/06/19              24                    =edate(A1,b1)  = answer 11/05/21

 

But is there a way without including column 'b'?

Hope Im making sense

TIA



-- Edited by Cheshire on Monday 29th of July 2019 01:31:25 PM

__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position



Master Book-keeper

Status: Offline
Posts: 8646
Date:

Actually that isnt going to work due to earlie mess ups!

1st date of payments for some have been late, so doubled up in first month, with date of first payment being eg 9 May, another on 15 May, D/D now set up for 15th monthly, so the formula above aint going to work.

Back to the drawing board!

__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position



Senior Member

Status: Offline
Posts: 259
Date:

Cheshire wrote:

Col a                    Col b                  Col c

11/06/19              24                    =edate(A1,b1)  = answer 11/06/21

But is there a way without including column 'b'?


Hi Joanne, 

Yes. =edate(A1,24)

Just for future reference.

Regards,



__________________

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel. No set-up necessary. Free 30 day trial.



Master Book-keeper

Status: Offline
Posts: 8646
Date:

Hi Ian
OMG - its so simple when you know!!

Or when you stop trying to make it difficult, which I seem to be doing with everything today.

Thank you so much Ian.

__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position



Master Book-keeper

Status: Offline
Posts: 8646
Date:

=edate (a1, 24-1) is useful for my excel.



__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position



Expert

Status: Offline
Posts: 1811
Date:

As a matter of curiosity, why do you want to avoid putting the number of months in a separate cell? I would have thought that more useful, because the formula then has the flexibility of the number of months being variable rather than hard-coded.

__________________

Vince M Hudd - Soft Rock Software

(I only came here looking for fellow apiarists...)



Master Book-keeper

Status: Offline
Posts: 8646
Date:

Just cos I don't like extra columns! Bonkers probably, but then I am.

In this case, all leases are 24 months.

Problem being in this case, they haven't set up a pile of payments when they should, so some clients paying twice in first month to catch up, hence the minus 1 in some cases for ease.

I'm hoping the whole thing will be redundant in a month or two when I've sorted it out and got it running automatically via the software and recurring invoicing set up to match everything.



-- Edited by Cheshire on Wednesday 31st of July 2019 08:28:28 AM

__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position

Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  
©2007-2024 The Book-keepers Forum (BKF). All Rights Reserved. The Book-keepers Forum (BKF) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Book-keepers Forum and BKF are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Book-keepers Forum. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Book-keepers Forum. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: bookcertltd@gmail.com.

Privacy & Cookie Policy  About