DUE DATE UPDATION & DUE PAYMENT REMINDER AS PER DATES

[Deepak Sharma]

HELLO Everyone,

I have ATTACHED a sheet! I need your help! I just want to Update my payment due date automatically as per cycle of payments & just want to set a reminder if that updated due date is occuring within next few days.


Please advice how to do it.

Thanks and Regards,
Deepak Sharma

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[bosco_yip]

Another option,

See attached file,

Regards
Bosco

 

[Deepak Sharma]

Hello Narayan/Bosco,

Thanks for your help guys! But what actually I am looking for is, I just need a simple reminder sign, NOT Reminder 1, 2, 3 etc., ONLY a reminder FEW DAYS Back like for 6-7 days before the due date, that this payment reminder needs to sent to the client.
Suppose next billing date is 30th July, 2017, so Alert should come around 23-24th of July'2017.
* I think BIANNUAL (SEMI ANNUAL) is calculating wrong in BOTH the files.
* In these scenerios it's getting remind on the EXACT Due date.
* If I am changing the start date then Next bill date is not chancing accurately.

Please see if something can happen....Thanks Again !!

 

[NARAYANK991]

Hi ,

Please upload a workbook with enough data in it , and mention the expected outputs.

Narayan

 

[Deepak Sharma]

Hi Narayank,
I have updated the file again. Pls. Check, Actually I want to open this file on daily basis, and in one go if any alert is there then just want to send that reminder through email to our clients that your payment is Due. what happens is while searching one by one atleast one payment gets missed always.
Please make sheet like that so that I can also add more projects to it.
Thanks for your help Narayank.

 

[NARAYANK991]

Hi ,

There are two or three requirements in your question :

1. A formula in column G , which indicates the next due date for various frequencies of billing.

2. A formula in column H , which displays a message Send Reminder , when today's date is 7 days or less than the next bill date in column G.

3. Conditional Formatting in column H , which colours a cell when it contains the text Send Reminder.

Your file does not contain any expected outputs in column G , when your earlier post specifically mentions that the solutions already posted have mistakes for the biannual frequency of billing.

Unless you post a workbook with the expected outputs , I am afraid I cannot do anything more.

Narayan

 

[NARAYANK991]

Hi ,

Updating the start bill date in column E can only be done using VBA , unless you wish to have a long table of bill due dates , and the initial start date in some other area of the workbook ; the start dates in column E will be derived from the initial start date and the table of bill due dates , using a formula.

Narayan

 

[Deepak Sharma]

Hi,

So sorry that you are afraid of me, actually I don't know how to do it through excel or VBA.

Yes you got me right in your 1st, 2nd and 3rd Points.

Yes you posted Column G outputs very right in 1st attempt, only Biannual had littlebith issue.

Actually I don't want to change the column E, just 1st to 3rd points as you mentioned. If it can be done in VBA, then pls suggest how to proceed.
In column H just a colored message asking to send reminder when next billing date falls in between 7 days from the current computer date.

For your reference I m also attaching another sheet inwhich I am currently maintaining. In this sheet I am manually updating the dates and payments.

Payments I can maintain separately in this sheet. BUT the sheet which I have shared with you in that I want to set reminders and date automation so that It can updated automatically.


Thanks!!!!

 

[NARAYANK991]

Hi ,

I think the mistake in the biannual billing cycle frequency has been corrected in this file.

Can you confirm ?

Narayan

 

[Deepak Sharma]

Hi Narayan,

So sorry for the late reply, Actually I was not well, thats why couldn't come to the office from so long.

Ya Biannual is working fine but now when I changed my system date to 15th Jan'18 then Quarterly Date in third row is getting wrongly updated.
Can you please look, it's showing of Jan's date, whereinas It should show 21st October'17.

Thanks for your help!