• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Put month end dates between two given dates

mahesh402

Member
I have a data with dates like 15/2/2018,13/3/1018,26/3/2018......
I want to put month end date between these dates without disturbing their sequence...I mean the answer should like 15/2/2018,28/2/2018,13/3/2018,26/3/2018,31/3/2018..
Any help would be a great help to me
 

mahesh402

Member
I am really thankful to you for giving time....but I tried to show in excel file that what kind of output I require..
Thanks in advance....
 

Attachments

mahesh402

Member
I mean I want month end date after all the dates in that month...
And if there is no date of month then still I require last date of month in order
thankss once again
I again attached the excel...plz have a look at it
 

Attachments

Last edited:

mahesh402

Member
Sorry for replying late...and thankyou for giving ur precious time...the output of this macro only gives the only eom date ...but the thing is I want the eom date to be adjusted in original sequence of input dates...as I depicted in output column of excel file...earlier
Thanks for once again
 
Last edited:

mahesh402

Member
I don't know if it is even possible or not through excel.....but if there is some way to it......then it would be saviour to me.....
 

vletm

Excel Ninja
mahesh402
Many things are possible...
but the 1st thing is to get a valid input ...
... there seems to be mixed input... dates ... which looks ... hmm? ( I ... find ... couples )
Could You use same format with all? (especially not texts - which looks like dates)
Screenshot 2020-12-28 at 10.28.44.png
 

mahesh402

Member
Sorry for that mixed input values..Yes I can use the same format with all...and this is the exact output I am looking for...kindly share the magic code
 

mahesh402

Member
Thanx for the code sir...but whenever I am trying to open this .xlsb file the computer prompts me that " excel found unreadable content and recover the content" and when I click on the recover then some content gets removed from the file....and due to which I am not able to get the proper output I required
 

mahesh402

Member
Xlsm file opened as it should be.....but when I pressed the DO IT button all input dates get together and and all eom dates comes in the last of input dates...I mean not as per the expected result...what I am doing wrong here...
If possible could you plz paste the original vba or macro code here or in text file
Thanx once again...
And sorry to bother you again
 

vletm

Excel Ninja
mahesh402
Do that sheet look as below after You've opened it?
Screenshot 2020-12-28 at 14.30.56.png
What will 'change' after You have pressed [ Do It ]-button?
If not as above then ... save that file and send it back here.
Per Your writing... it seems that E&F-columns won't sort.
The code is in that file - You can get it from there.
>>>
mahesh402
Here other sample version without sorting by adding those missing dates.
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
This sort of thing can also be done using MS365. I try to make the solution as pedantic as possible with few tricks.
Code:
= LET(
  start, EOMONTH(MIN(InputDates),-1),
  end, EOMONTH(MAX(InputDates),0),
  m, 1+DATEDIF(start, end, "m"),
  n, COUNT(InputDates),
  k, SEQUENCE(m,,0),
  i, SEQUENCE(m+n),
  monthEnds, EOMONTH(start, k),
  appended, IF(i<=n, INDEX(InputDates, i),  INDEX(monthEnds, i-n)),
  dates, UNIQUE(SORT(appended)),
  IF({1,0}, dates, XLOOKUP(dates, InputDates, InputValues, "") ) )
72708
 

John Jairo V

Well-Known Member
Hi to all!

Another option could be:
Code:
=LET(d,A3:A8,v,B3:B8,
em,UNIQUE(EOMONTH(+d,0)),
df,AGGREGATE(15,6,IF({1,0},d,em),SEQUENCE(COUNT(d)+COUNT(em))),
IF({1,0},df,XLOOKUP(df,d,v,"")))
Blessings!
 

mahesh402

Member
mahesh402
Do that sheet look as below after You've opened it?
View attachment 72706
What will 'change' after You have pressed [ Do It ]-button?
If not as above then ... save that file and send it back here.
Per Your writing... it seems that E&F-columns won't sort.
The code is in that file - You can get it from there.
>>>
mahesh402
Here other sample version without sorting by adding those missing dates.
yes it looked like as per the above picture but when it pressed [Do it] button it becomes like this...
just plz look it into the excel file and tell where did it went wrong...
Thankyou sir
 

Attachments

Last edited:

mahesh402

Member
Hi to all!

Another option could be:
Code:
=LET(d,A3:A8,v,B3:B8,
em,UNIQUE(EOMONTH(+d,0)),
df,AGGREGATE(15,6,IF({1,0},d,em),SEQUENCE(COUNT(d)+COUNT(em))),
IF({1,0},df,XLOOKUP(df,d,v,"")))
Blessings!
do I have to paste above code in excel cell????
I am using ms excel 2007...kindly tell me code compatible to that
 

vletm

Excel Ninja
mahesh402
based #18 - seems not sorted
Could be some connection with Your dates with input - which were something ...?

Did You test my other version from #15 reply?
It's without sorting!
 

mahesh402

Member
Its working!!!!!! My lord thankyou very very very much...You just saved me from disaster.....thankyou thankyou thankyou.....
 

John Jairo V

Well-Known Member
Hi again!
I am using ms excel 2007...
Allways put your excel version when you need some help.

Check this formulas:
[D2] : =IFERROR(SMALL(IF(IF({1,0},A$2:A$8,EOMONTH(+A$2:A$8,0))>MAX(D$1:D1),IF({1,0},A$2:A$8,EOMONTH(+A$2:A$8,0))),1),"")
[E2] : =IF(D2="","",IFERROR(VLOOKUP(D2,A$2:B$8,2,),""))

D2 is an array formula. Must introduce with CSE (Ctrl + Shift + Enter) instead of just Enter.
Check file too. Blessings!
 

Attachments

Top