获取每个月的第三个星期一的日期 [英] To get date of 3rd monday of each month

问题描述

我想创建一个宏，将在Excel中添加一列.它将检查日期是否为当月的第3个星期一.根据条件，它将在我的excel的最后3个位置添加一个列.

Excel将仅包含6个月的数据.
因此，每个月都会删除一列，并在excel中添加一列.
请让我知道如何使用宏来完成此操作，因为我无法获取每个月的第3个星期一的日期.

另外，是否可以在单个按钮上定义多个宏?

感谢所有帮助.


在此先谢谢您.在此日期前加上14，即可获得第三个星期一.

HTH

要在工作表的开头添加一列，您只需键入类似下面的代码即可:

ActiveSheet.Columns(1).EntireColumn.Insert

完成后，您只需键入:

ActiveSheet.Columns(ActiveSheet.UsedRange.Columns.Count).EntireColumn.Delete

删除最后使用的列.

以下是您获取星期几的方式:
http://msdn.microsoft.com/en-us/library/system.datetime. dayofweek.aspx [ ^ ]

您应该很容易地浏览一个月中的第一个日期，而只需计算三个星期一.

我实际上没有得到的这个问题:

报价:

还可以在单​​个按钮上定义多个宏吗?

您可以在单个宏中编写尽可能多的代码，也可以在一个excel文件中包含多个宏，因此我不明白您在这里的意思...


我希望这会有所帮助:)

Hi,

I want to create a macro that will add one column in my excel. It will check if the date is 3rd monday of the current month. Depending on the condition it will add a column in my excel at the 3rd last position.

The excel will contain 6months data only.
So every month a column is deleted and one column is added in the excel.
Please let me know how this can be done using macro as i am not able to get date for 3rd monday of each month.

Also, it is possible to defined multiple macros on a single button?

Appreciate all the help.


Thanks in advance.

解决方案

Find out the first Monday - start with day 1 of the month and check the week day in a loop until you hit Monday. Add 14 to this date to get the 3rd Monday.

HTH

For adding one column at the beginning of the sheet you simply typesomething like the code below:

ActiveSheet.Columns(1).EntireColumn.Insert

And after you have done that you simply type:

ActiveSheet.Columns(ActiveSheet.UsedRange.Columns.Count).EntireColumn.Delete

to delet the last used column.

Here is how you get the day of week:
http://msdn.microsoft.com/en-us/library/system.datetime.dayofweek.aspx[^]

It should be easy for you to loop through the first date in a month, and simply count three Mondays.

This question here I dont actually get:

Quote:

Also, it is possible to defined multiple macros on a single button?

You can write as much code as you like in a single macro, you could also have several macros in one excel file, so I dont understand what you mean here...


I hope this helps :)

这篇关于获取每个月的第三个星期一的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！