英文:
VBA - How can I get all Mondays in a month then multiply to working hours?
问题
我试图弄清楚如何计算一个月中星期一的总数，然后乘以工作小时。
这是我的示例代码，它可以运行，但计算结果不正确：
If UCase(val) Like "EVERY MONDAY" Then
Dim numString As Integer
Dim strDays() As String
Dim wordCount As Long
numString = 2
strDays = VBA.Split(val, " ")
wordCount = UBound(strDays)
strWhEveryDay = ThisWorkbook.Sheets("Input").Cells(X, 4).Value
strWhEveryDay = strWhEveryDay * var_month
Debug.Print "Every = " & strWhEveryDay
解释：
-
它取决于用户在文本框中输入什么。然而，计算取决于用户在文本框中输入的日期。
-
我有一个文本框，用户在其中输入日期格式，如下所示：
Jan-2023或Feb-2023
-
我有一个表格，如下所示：
将此文本放入表格中，从列B行2开始：
每周一
将此文本放入表格中，从列D行4开始：
1.2
-
我只需要根据给定的月份和年份获取所有星期一的总数。一旦我将文本从“每周一”更改为“每周二”，计算就会自动调整或自动知道计算天数从哪里开始和结束：
-
预期计算示例：每周一（2023年1月=5天）* 1.2，因此预期结果将为5。
注意： 使用Debug.Print查看结果或输出。
英文:
I'm trying to figure out how to get the total of Mondays in a month then multiply by working hours.
This is my example code it works but it counts wrong output:
If UCase(val) Like "EVERY MONDAY" Then
Dim numString As Integer
Dim strDays() As String
Dim wordCount As Long
numString = 2
strDays = VBA.Split(val, " ")
wordCount = UBound(strDays)
strWhEveryDay = ThisWorkbook.Sheets("Input").Cells(X, 4).Value
strWhEveryDay = strWhEveryDay * var_month
Debug.Print "Every = " & strWhEveryDay
Explanation:
-
It depends on the user if what they like to input in a TEXTBOX. However, the CALCULATION it depends on the date where the user input in TEXTBOX.
-
I have Textbox which is the target month where the user input the format of date like this:
**Jan-2023 or Feb-2023 **
-
I have a table like this:
Place this text in a table start in Column B Row 2:
**every Monday**Place this text in a table Column D Row 4:
**1.2** -
All I need is to get all the total of Mondays based on the given month and year. The calculation of the days in a table of "every Monday" once I change the text from "every Monday" to "every Tuesday" so the calculation will adjust or automatically knows where the calculation days start to end:
-
Example of expected calculation: every Monday (January 2023 = 5 Days) * 1.2 so, the expected result will be 5.
Note: use Debug.Print to see the result or output
答案1
得分: 1
So, using networkdays.intl() as suggested in my comment:
NETWORKDAYS.INTL($A$3,$A$33,"0111111",)
The result shown is 5, which is correct by inspection, for cells A3:A33 the long date format was used.
So multiplying by 1.2 is:
NETWORKDAYS.INTL($A$3,$A$33,"0111111",)*1.2
and 5 * 1.2 = 6
Also, the string "0111111" can be put in cell F5 and referred to so it is easier to edit.
英文:
So, using networkdays.intl() as suggested in my comment:
NETWORKDAYS.INTL($A$3,$A$33,"0111111",)
The result shown is 5, which is correct by inspection, for cells A3:A33 the long date format was used.
So multiplying by 1.2 is:
NETWORKDAYS.INTL($A$3,$A$33,"0111111",)*1.2
and 5 * 1.2 = 6
Also, the string "0111111" can be put in cell F5 and referred to so it is easier to edit.
答案2
得分: 1
我知道识别任何一天是星期几的最简单方法如下：
=WEEKDAY(B2,2)
“2”表示从“星期一”开始计数，将其视为1，“星期二”视为2，依此类推。
因此，如果你想知道日期是否是星期一，你可以使用这个公式：
=IF(WEEKDAY(B2,2)=1,...)
这可以很容易地转化为VBA，使用标准的IF语句。
英文:
The easiest way I know to recognise any day of the week is the following:
=WEEKDAY(B2,2)
The "2" means that weekdays are counted, starting with "Monday" as 1, "Tuesday" as 2, ...
So, if you want to know if your date is a Monday, you can use this formula:
=IF(WEEKDAY(B2,2)=1,...)
This can easily be translated into VBA, using a standard IF-clause.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论