Excel - Working with VBE and Calculating End date based on Start Date and duration of days.

Question

0.00/5 (No votes)

See more:

Hi.

This is my first vba task. What i need to do is, calculate end date based on the start date and number of days.

I have the values for start date in column A and duration i.e.; number of days in column B. Calculating business days is my second concern as of now. What I first need to know is, how can i use Worksheet_Change event?

What i thought of doing is, when user enters duration in any cell in column A say A(n), Cell B(n) gets selected in the Worksheet_Change event code. And when Start Date is entered in B2, function to calculate End date is called. Please let me know if its a correct approach or not.

Also what caused trouble for me was, when I press Enter key after entering values in cells, the Worksheet event behaves abruptly.

Please help me solve this simple yet complex issue for me!

Posted 21-Mar-13 5:43am

Mrilika Sahay

Add a Solution

Comments

Richard C Bishop 21-Mar-13 11:46am

Simple and complex do not go toether. If it is simple, it is simple, therefore not complex. If it is complex, it is not simple. What have you tried so far, where is your code?

CHill60 21-Mar-13 12:29pm

This seems a rather heavy-handed way of doing things ... what's wrong with just putting a formula into the worksheet to immediately calculate the end date?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-03-21T06:52:00

You don't need any vba code. Remeber Excel stores dates as numeric values. So, all you need to do is to create formula, like this:
C1 = A1 + B1, where
C1 is cell, in which formula is stored,
A1 is cell with date,
B1 is cell with number of days

To get more information, read this: How to use dates and times in Excel[^]

To calculate end date in VBA, use DateAdd()[^] function.