Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB Excel
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 6:43am
Comments
richcb at 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 at 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
good
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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 406
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 205
3 Raul Iloc 170
4 Maciej Los 164
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 21 Mar 2013
Copyright © CodeProject, 1999-2014
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