Click here to Skip to main content
12,996,709 members (78,205 online)
Rate this:
Please Sign up or sign in to vote.
See more:

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
richcb 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

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170609.2 | Last Updated 21 Mar 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100