Find end of month between two dates

Question

0.00/5 (No votes)

See more:

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

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.

Posted 18-Jun-15 4:49am

Sundar9057

Updated 18-Jun-15 8:49am

Maciej Los

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

DamithSL · Answer 1 · 2015-06-18T05:08:00

Solution 1

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

=EOMONTH(A1,0)

Posted 18-Jun-15 5:08am

DamithSL

Comments

Andy Lanng 18-Jun-15 11:15am

DOH! you beat me to it :Þ

Maciej Los 18-Jun-15 14:49pm

5ed!

Sergey Alexandrovich Kryukov · Answer 2 · 2015-06-18T05:16:00

Solution 2

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

Posted 18-Jun-15 5:16am

Sergey Alexandrovich Kryukov

Comments

Sundar9057 18-Jun-15 11:22am

Hello, I have included the result needed for the mentioned problem. Can anyone help me required steps. I'm unable to follow you :(

Sergey Alexandrovich Kryukov 18-Jun-15 11:24am

What have you tried so far?
Which part of my explanation is unclear?
—SA

Sundar9057 18-Jun-15 11:34am

I need to have a unique combination of month and year for the first range sorted in descending order and then find the last day for each month and year. I'm unaware how to achieve this in excel with formula alone. any vba code to compute this will also help. thank you

Sergey Alexandrovich Kryukov 18-Jun-15 11:58am

I just explained how to achieve it; did you try it out?
—SA

Sundar9057 18-Jun-15 12:00pm

yes. but the dates are not continuous. there may be missing months for any given year. I've gone blank

Sergey Alexandrovich Kryukov 18-Jun-15 12:09pm

There are no "missing month". What are you talking about? What have you tried, exactly?
Does your Excel version have this function?
—SA

Sergey Alexandrovich Kryukov 18-Jun-15 12:10pm

There are no "missing months". What did you try, exactly? Do you have this function in your version of Excel?
—SA

Sundar9057 18-Jun-15 13:26pm

When we have the first range of dates compared to avail the end of months, there could be dates unavailable for a period in the first range. I'm seeking help to arrive at a desired solution. If I could solve this myself, there was no necessity for me to post anything here. I'm looking for a solution if someone is willing to help and not for others to dominate over my situation. Thank you very much.

Sergey Alexandrovich Kryukov 18-Jun-15 15:05pm

I just gave you the answer. The only problem is: does your version of Excel provide this function?
—SA

Maciej Los 18-Jun-15 15:03pm

Clear enough, 5!
Please, see my solution ;)

Sergey Alexandrovich Kryukov 18-Jun-15 15:06pm

Thank you, Maciej.
—SA

Maciej Los · Answer 3 · 2015-06-18T09:02:00

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

VB

Function GetEofMonth(ByVal d As Date) As Date

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

End Function

Usage:

VB

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