EXtremly Urgent Please


Hi guys!

I was wondering if it was possible on Excel to set up a formula that would automatically update when a contract is ending.

For example one contract has a 6 months contract that starts at 1/1/2018 and must end at 31/6/2018..So the contract should roll over and automatically renews on a monthly .Also I have some contracts starting mid of every months.

Is there a way to set up the formula where it would update the Current End Date of the contract automatically?

We have a lot of contracts and it would be really helpful if it would update itself automatically, so we wouldn't miss when a contract is coming due.



If you have an urgent issue, message the site through the contact form and you can receive paid help there.
don (rep: 1745) Dec 13, '17 at 11:34 am
As for this question, please update your title to reflect the question that you are asking. Also, how do you want to 'update' the current end date? I don't even see a field with that name.
don (rep: 1745) Dec 13, '17 at 11:36 am
I want to update the Current End Date to be my New Starting Date
SHALI (rep: 2) Dec 14, '17 at 1:40 am
Sorry but that's really not specific enough. What cell should be changed to what and where and how did you want the calculation to be made? I recall the workbook only having 1 date per line.
don (rep: 1745) Dec 18, '17 at 5:39 pm
Hi Don
I will provide a new workbook with details you asked for.Please check it
SHALI (rep: 2) Dec 19, '17 at 5:14 am
Add to Discussion


Selected Answer

Since your question is whether it is possible, the answer is "yes". If your question is then extended to whether I can help you, my answer would be "probably".

First, please explain the logic. What is the point in having an end date if the end date is moved without user action? Would full automation not destroy the purpose of the sheet's information? Perhaps you like an extra column, like, "original end date" and "rolled-over end date".

Second, logically every end date must be calculated from a start date. If you want the old end date to be the new start date the task requires VBA. The change wouldn't be fully automatic like a formula. The change would be made every time you open the worksheet. That is probably good enough for your requirement. Therefore the big issue is whether you want VBA or not. If you want a formula-based solution you need to have a start date that never changes (meaning an extra column in your worksheet). The end date would always be calculated based on that date.

Third, both formulas and and VBA need precise rules how the end date is calculated from the start date (even if the old end date is the current start date). Your formula in F3 gives a hint but hints aren't suitable substitutes for precisely expressed rules.



Thanx Variatus,
Yes I think you are correct,VBA type of work sheet would work great for my requirement where I open it & come to know the current End Contracts.
2-YES.End date are calculated based on start date & Old End date become new start date,But in some cases we have to make new start date.
Variatus please help me out....Really appreciate your reply.
SHALI (rep: 2) Dec 14, '17 at 1:19 am
Two scenarios, Shali.
1) I write code for you in the workbook you have provided. My code will do what I think you want but it won't do it the way you want because the workbook you published doesn't have the columns you need. You will ask me to amend it, giving me the information you don't provide now. At that point I will tell you that your question was asked and answered, and I provide free help, not free code-writing service. We will both have wasted our time.
2) You take the answer you got in this thread, mark the answer as accepted and close this thread. With the knowledge gained from here you formulate a new question with which you publish a suitably amended worksheet, and in the worksheet you show exactly in which column you want which information and how to calculate it. In that way I will save time, you will get exactly what you want, and the forum is the richer for having answered two questions well.
Let me know which way you prefer. If you are good at VBA you might prefer the first one and tweak the code yourself after I provide it.
Variatus (rep: 3378) Dec 14, '17 at 4:36 am
SHALI (rep: 2) Dec 17, '17 at 3:03 pm
i cant find a link to upload my workbook
SHALI (rep: 2) Dec 17, '17 at 3:10 pm
Please don't use all-caps. I perceive the use of all-caps as very rude.
Variatus (rep: 3378) Dec 17, '17 at 9:59 pm
You should make a fresh question and attach the revised workbook to the new question, not this discussion. This thread is closed already.
Variatus (rep: 3378) Dec 17, '17 at 10:00 pm
Ok thanx
SHALI (rep: 2) Dec 19, '17 at 5:12 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login