Excel - 使用VBE并根据开始日期和持续时间计算结束日期。 [英] Excel - Working with VBE and Calculating End date based on Start Date and duration of days.
问题描述
嗨。
这是我的第一个vba任务。我需要做的是,根据开始日期和天数计算结束日期。
我有A栏中的开始日期和持续时间的值,即; B栏中的天数。计算工作日是我目前的第二个问题。我首先需要知道的是,我如何使用Worksheet_Change事件?
我想做的是,当用户在A列的任何单元格中输入持续时间时说出A (n),在Worksheet_Change事件代码中选择单元格B(n)。当在B2中输入开始日期时,将调用计算结束日期的函数。如果它是正确的方法,请告诉我。
还有什么给我带来麻烦的是,当我在单元格中输入值后按Enter键时,工作表事件表现突然。
请帮我解决这个简单而复杂的问题!
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!
推荐答案
你不要不需要任何vba代码。 Remeber Excel将日期存储为数值。所以,你需要做的就是创建公式,如下所示:
C1 = A1 + B1
,其中
C1
是单元格,其中存储公式,
A1
是带日期的单元格,
B1
是具有天数的单元格
要获得更多信息,阅读:如何在Excel中使用日期和时间 [ ^ ]
要计算VBA中的结束日期,请使用 DateAdd() [ ^ ]功能。
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.
这篇关于Excel - 使用VBE并根据开始日期和持续时间计算结束日期。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!