• 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
Its working!!!!!! My lord thankyou very very very much...You just saved me from disaster.....thankyou thankyou thankyou.....
Sorry to bother you again...I am also grateful for your help...I know I did not mention it earlier...but is there any way that I can also get the the sum of values in particular month on eom date of that month...I mean like in pic below please see into it.. values in bold are eom dates and the sum of the values in that particular month72727
 

vletm

Excel Ninja
mahesh402
Were You over 50% sure that there won't be any values ever in the end of month dates?
As well as You would have unknown number of rows of data every time ...
My sample version ...
 

Attachments

Peter Bartholomew

Well-Known Member
Message #4
<And if there is no date of month then still I require last date of month in order>
Doesn't that imply the end of months with zero transactions are still required?

@John Jairo V
You have posted radically different solutions. Which is your personal preference (assuming a version of Excel that will run each)?
 

Peter Bartholomew

Well-Known Member
The formula is getting long!
Code:
= LET(
  start, EOMONTH(MIN(InputDates),0),
  end,   EOMONTH(MAX(InputDates),0),
  m, 1+DATEDIF(start, end, "m"),
  n, COUNT(InputDates),
  k, SEQUENCE(m,,0),
  i, SEQUENCE(m+n),
  monthStarts, EOMONTH(start, k-1),
  monthEnds,   EOMONTH(start, k),
  monthlyTotals,  SUMIFS(InputValues, InputDates, ">="&monthStarts, InputDates, "<="&monthEnds),
  appendedDescr,  IF(i<=n, "Entry",  "Subtotal"),
  appendedDates,  IF(i<=n, INDEX(InputDates, i),  INDEX(monthEnds, i-n)),
  appendedValues, IF(i<=n, INDEX(InputValues, i),  INDEX(monthlyTotals, i-n)),
  dates, UNIQUE(SORT(appended)),
  combined, CHOOSE({1,2,3}, appendedDescr, appendedDates, appendedValues),
  SORT(UNIQUE(combined),2)  )
72735
 

mahesh402

Member
mahesh402
Were You over 50% sure that there won't be any values ever in the end of month dates?
As well as You would have unknown number of rows of data every time ...
My sample version ...
I am already grateful to you for helping me in this...This sheet is perfect.....but my problem is to get the sum of values in the same column of values as I depicted in #27 and if possible please also add the difference of days between two adjacent dates in the column next to values...after this I won't ask any modifications in this thread...I spent whole day yesterday trying to modify with no luck....but as I already said I am already grateful to you for all the help
 

mahesh402

Member
The formula is getting long!
Code:
= LET(
  start, EOMONTH(MIN(InputDates),0),
  end,   EOMONTH(MAX(InputDates),0),
  m, 1+DATEDIF(start, end, "m"),
  n, COUNT(InputDates),
  k, SEQUENCE(m,,0),
  i, SEQUENCE(m+n),
  monthStarts, EOMONTH(start, k-1),
  monthEnds,   EOMONTH(start, k),
  monthlyTotals,  SUMIFS(InputValues, InputDates, ">="&monthStarts, InputDates, "<="&monthEnds),
  appendedDescr,  IF(i<=n, "Entry",  "Subtotal"),
  appendedDates,  IF(i<=n, INDEX(InputDates, i),  INDEX(monthEnds, i-n)),
  appendedValues, IF(i<=n, INDEX(InputValues, i),  INDEX(monthlyTotals, i-n)),
  dates, UNIQUE(SORT(appended)),
  combined, CHOOSE({1,2,3}, appendedDescr, appendedDates, appendedValues),
  SORT(UNIQUE(combined),2)  )
View attachment 72735
every time I entered the this code with control+shift+enter and replacing the input dates with date range and input value with value range...but it shows error could you please tell how to apply above code in ms excel 2007 . ....but yes I want exactly this output.... thanks once again for giving your precious time to solve my problem
 

vletm

Excel Ninja
mahesh402
Did You answer to my question? ... or notice it at all?
Were You over 50% sure that there won't be any values ever in the end of month dates?
eg if You have value 111 already in 28-Feb-2018 ( yellow cells )
Which value would You want to have in 28-Feb-2018 ... 111 or 211?
How would You know that there were something before?
Screenshot 2020-12-31 at 09.09.38.png
... difference of days ... means like above (with extra sample end of month 28-Feb-2018 -value)
 

mahesh402

Member
mahesh402
Did You answer to my question? ... or notice it at all?
Were You over 50% sure that there won't be any values ever in the end of month dates?
eg if You have value 111 already in 28-Feb-2018 ( yellow cells )
Which value would You want to have in 28-Feb-2018 ... 111 or 211?
How would You know that there were something before?
View attachment 72744
... difference of days ... means like above (with extra sample end of month 28-Feb-2018 -value)
sorry for not answering previously...I read your question with all my attention but I have too many things going on so I just forgot to mention..so yes there may be chance in one or two cases out of around 65 cases in hand where I have values on the eom but if there are values then I require
two times eom date in that particular case one with value and one eom date with sum of values and yes that is the exactly the difference of days I was asking....please see the image


72747
 

vletm

Excel Ninja
mahesh402
Your: ... I ( mahesh402 ) spent whole day yesterday trying to ... all my attention but I have too many things going on so I just forgot to mention ...
And with clear answer, You would have time to do ...
I did my version, because there seems to be still some missing rules.
You could see some of those from this file.
... eg difference of days based input
... and if Your activecell is C1 before press [ Do It ] , then there would be extra values to show.
... if You will press more that once [ Do It ] ...
 

Attachments

mahesh402

Member
mahesh402
Your: ... I ( mahesh402 ) spent whole day yesterday trying to ... all my attention but I have too many things going on so I just forgot to mention ...
And with clear answer, You would have time to do ...
I did my version, because there seems to be still some missing rules.
You could see some of those from this file.
... eg difference of days based input
... and if Your activecell is C1 before press [ Do It ] , then there would be extra values to show.
... if You will press more that once [ Do It ] ...
your version is great.....superb and excellent......but just a little thing is that the no of days after pressing [Do it] button in column C are not proper after 7 th row but the expected outcome of days should be like in column D...plz see pic
rest in your version is all great and that solves my problem....and yes thanks for all the help....I am grateful to you...I actually don't have words to express my gratitude....and ofcourse HAPPY NEW YEAR 2021 ...I pressed 2 times [Do it] button

72771
 

vletm

Excel Ninja
mahesh402
What would those C-column numbers show?
Differences of days between between values OR whatever dates?
Screenshot 2021-01-01 at 10.46.57.pngeg 91 ... is differences from 15-Apr-2018 to 15-Jun-2018 based Your given values.
 

mahesh402

Member
mahesh402
What would those C-column numbers show?
Differences of days between between values OR whatever dates?
View attachment 72773eg 91 ... is differences from 15-Apr-2018 to 15-Jun-2018 based Your given values.
Yes sir you are completely right but please see #33 ( I requested that sum should be in same column of values...but days column was right in that reply(#33).....)I mean I do not require the days between the original dates but after applying eom dates..this is what I tried to show in column D(#36) and this is exactly in the reply #33(only days column part)...Thanks for help again
 

GraH - Guido

Well-Known Member
Sorry, maybe it's still too early in the new year for my head to tackle Excel issues, but why all this complication while a simple pivot seems to answer the need?
72776
 

mahesh402

Member
Sorry, maybe it's still too early in the new year for my head to tackle Excel issues, but why all this complication while a simple pivot seems to answer the need?
View attachment 72776
You are right!!!!! but the data I have in hand is not that simple moreover I need this code because the result from this codes will becomes input for the further process... I mean I know the further process once I got the code as per request.....I could have shared the data but due to some office restriction I am unable to share otherwise you guys are awesome has already solved the 95% of my problem and I am thankful to you already...
 

vletm

Excel Ninja
mahesh402
You write something and next You're writing something else?
... and now, You seems to be happy with something else too? .. or not?
... .... which You didn't accept few days ago ... hmm? ... or not?
Did You answer to my previous questions?
Without answers ... I don't know ... If I don't know ... Should I guess something?
or do some variations for my own purpose? (which I ... should I need those?)
 
Top