查找两个日期之间的月末 [英] Find end of month between two dates

问题描述

我在excel表中有以下日期

Hi,
I have following dates in a excel sheet

5/27/2015
5/26/2015
4/25/2015
2/24/2015
5/23/2015
5/22/2014
3/21/2014
2/20/2014
6/19/2014
7/18/2013

我需要找到上述日期的月末在另一张表中。所以，我的输出应该是

I need to find the end of months for the above dates in another sheet. So, my output should be

5/31/2015
4/30/2015
2/28/2015
6/30/2014
5/31/2014
3/31/2014
2/28/2014
7/31/2013

任何VBA代码或excel公式都会有所帮助。并且还认为第一个表格中有另一个感兴趣的列，并且包括在第二个表格中表达的一个月的未完成权益将有很大帮助。



感谢您的宝贵解决方案。

Any VBA code or excel formula would help. and also thinking that first table has another column with interest and including the Outstanding Interest accured for a month in the second table will help tremendously.

Thank you for your valuable solutions.

推荐答案

尝试使用EOMONTH函数，例如你可以计算月份单元格值的结尾为

try with EOMONTH function for example you can calculate month end of A cell value as

=EOMONTH(A1,0)

该算法众所周知，并在许多地方得到了推荐。您可以使用 DATEADD ：

http://www.techonthenet.com/excel/formulas/dateadd.php [ ^ ]。



想法是：



假设你有一些日期 D 。添加一个月，然后减去一天。您将在 D 月的最后一天到达。这是它的主要部分。



要到下个月，请将日期D设置为其当月的第一天，并重复上述步骤。在循环中重复检查当前日期是否仍在所需的时间范围内。



这就是全部。

-SA

The algorithm is well known and was advised in many places. You can use DATEADD:
http://www.techonthenet.com/excel/formulas/dateadd.php[^].

The idea is:

Let's say you have some date D. Add one month and than subtract one day. You will arrive at the last day of the month of D. This is the main part of it.

To get to next month, set the date D to the first day of its month and repeat the step explained above. Repeat it in loop checking if your current date is still inside required time frame.

That's all.

—SA

除了 DamithSL [ ^ ]和解决方案2 Sergey Alexandrovich Kryukov [ ^ ]这里是<$ c $的实现c> EOMONTH 公式（函数）：

In addition to the solution 1 by DamithSL[^] and solution 2 Sergey Alexandrovich Kryukov[^] here is an implementation of EOMONTH formula (function):

Function GetEofMonth(ByVal d As Date) As Date

    GetEofMonth = DateSerial(Year(d), Month(d) + 1, -1)

End Function

用法：

Usage:

Sub Whatever()
Dim wsh As Worksheet
Dim i As Integer

Set wsh = ThisWorkbook.Worksheets("Sheet1")
i = 2
Do While wsh.Range("A" & i)<>""
    wsh.Range("B" & i)= GetEofMonth(CDate(wsh.Range("A" & i))) 'returns 5/30/2015
    i = i+1
Loop
Set wsh = Nothing
End Sub

这篇关于查找两个日期之间的月末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！