• 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.

Split a date range in 30 steps with ~equal size

Hi Experts,


Lets me write in simple. I have two dates and want split this range in "N" number slots.


Example


First date
Code:
1-Jan-12

Last date 10-Oct-12


If i split this range in 30 equal step i get a step of 9.1. Date can not be incremented in friction without considering time value. i tried using round up or round down i get 9 or 10 while will lead me a different end date after 30 steps which is fixed.


I need to split this date range in "N" number of slots ensuring that:


1) Step should be of ~equal size ( it can roundoff to get the integer number)

2) No date should be missed

3) First date & end date should be matched


Regards,
 
Hi Kuldeep ,


How do you want these 'slots' ? Do you want that the 30 dates should be put in 30 cells ? Can you use helper columns ?


Narayan
 
Hi KJP,


To me, this will only be possible only if the No. of days is the multiple of N. For your case it is 9.13 hence it is not an exact multiplier. There will be some steps that will be shorter/larger then others.


Regards,

Faseeh
 
@ Narayan, I am free to use helper coloum. yes these will be put in 30 predefined cells.


@ Fasseh, I agree and that only caused me knock you experts, if you look in point 1
Code:
"Step should be of ~equal size
" i asked to have approximate equal slots & not exactly equal. (if this would have been possible all our math will go in ruin :))


I am expecting that these larger/shorter slots to be adjusted (with in range of +/- one) in between and to reach on exact end date.


Regards,
 
Thanks Narayan,


This is the only requirement (We are in right direction) but this solution fails if we just put the end date as as 28-10-2012 or 27-11-2012 & see how it fails as the number in E1 actually become a integer.


Another one is that last row value is varying from widely. Use the 26-10-2012 as end date, we get a sudden addition of 29 days. This is because as the value in E1 approaches near to upper band it do not take care of it and just add in last while these 29 days can be shared with upper 29 rows.


Regards,
 
Hi Kuldeep ,


Can you say what is the range of dates you expect to work with ? Is a VBA based solution acceptable ?


Narayan
 
Hi Narayan


VBA Acceptable but formulas preferable


About dates


I expect to use dates with in a financial year for 90% of time but for the worst case start date can be taken from 1-1-2009 to 1-1-2020.


Regards,
 
Hi KPJ,

Not sure if I understood your requirements, but could you try something like the following?

=INDEX(BeginDate + (((EndDate-BeginDate)/(Interval-1))*(ROW($A$1:INDEX($A:$A,Interval)) - 1)), ROW(1:1))


Copy it down to additional rows as needed.


I did not include any error checking, but that should be straightforward.


Hope this helps.


Cheers,

Sajan.
 
Hi KPJ,

My previous formula can be simplified as follows:

=IF(ROW(1:1)<=Interval, BeginDate + ((EndDate-BeginDate)/(Interval-1))*(ROW(1:1)-1), "…")

Copy it down to additional rows as needed.


If you want to display the dates in columns, replace ROW(1:1) with COLUMN(A:A)


(The formula now also includes a simple error check.)

(By the way, this results in a total of "N" dates, including the Begin and End dates. If that is not what you wanted, we can tweak the formula slightly.)


Cheers,

Sajan.
 
Ah... Sajan,


This does not work. please apply the formula to the narayan's file and see. Might be i made a mistake in understanding solution or you understanding to problem.


Regards,
 
Hi Kuldeep,


Can you please check the attache file, with Sajan's Formula.. Its working..

https://dl.dropbox.com/u/78831150/Excel/KPJ_Book2.xlsx


@ Sajan:

Dont worry about the Start & End Date.. you may have missed this line.. :)

3) First date & end date should be matched

and all three conditions are pefectly matched..

Still.. let the OP speak.. :)


Regards,

Deb
 
Hi Deb ,


As you say , let Kuldeep speak ; however , having gone through your file , I can say that there is a slight difference between what I have done , and what Sajan has done.


If you see Sajan's results , all of them have a time component , which the format does not reveal ; the results I have are all strictly integral dates , without any time component ; I thought this was the original specification , since if this is not required , getting 30 date and time values should be a simple matter of dividing the difference between the End and Start dates by 30 !


Narayan
 
So kind of you guys,


Narayan point out the catch. I need the strictly dates adjusted by +/- one day and to make duration and reach to final date.


A bit of background which lead to this requirement.


I have a data base with varying from 100- 50K row having date in one coloum. (it just the date with no time stamp) extracted from oracle.


Now i need to count the events in 30 ~ equal time frames. I tired to divide this by using
Code:
=$D$9-$E$8+ROWS($B$13:B13)*$E$9
where D9=Start date E8 = end date and E9= step size which caused the same problem which will occur with Sajan's solution.


Over to you guys...


Regards,
 
Hi Kuldeep ,


Let me put down your requirements in my own words :


1. Suppose the start date is January 1 , 2012.


2. If we take that the slot width cannot be less than 9 days or greater than 10 days , then , the minimum date possible is September 27 , 2012 , while the maximum date possible is October 27 , 2012 ; I am assuming that the first slot is Jan-10 or Jan-11 , and the 30th slot is Sep-27 or Oct-27 i.e. we exclude the start date , and include the end date. Is this correct ?


3. Any date outside these two limits will be invalid , since it will not meet the criterion of every slot width being greater or equal to 9 , and less than or equal to 10 days.


4. The dates have to be integer dates , without any time component.


Can you confirm or clarify on the above ?


Narayan
 
Hi Narayan,


Slightly different requirement.


1)We will start from the start date Lets say 1-Jan-2012

2)We will end on End date Lest say 10-Oct-2012

3)Difference between start date and end date will more 30 days (To keep it simple)

4) Step size can vary from 1 to hundreds of days.

5) Step should be of approximate equal size, Example if we have 65 days gap between start date and end date assuming we are dividing it in 30 equal step we have step size of 2.XX days. Now i have 5 days to adjust in these 30 slots. i am happy to add 1 day at each 6th slot but end date has to be matched.

6)The dates have to be integer dates , without any time component.


Hope i am clear a bit now.


Regards,
 
Hi Kuldeep ,


Thanks for the clarifications ; let me see if I can add more clarity.


The following are given :


1. Start date A

2. End date B

3. Slot Size cannot be less than C

4. Slot size cannot be greater than D

5. Number of slots required E


All slots have to be integer dates.


It goes without saying that all of the above , 1 through 5 , are configurable ; the user can modify any or all of them.


However , given the above , it follows that :


a. ( B - A ) cannot be less than E * C


b. ( B - A ) cannot be greater than E * D


These two points , a and b , are what I would like you to confirm or clarify.


Narayan
 
Specifications Approved...:)


However I see these variable as


1. Start date A

2. End date B

3. Slot Size cannot be less than C

4. Slot size cannot be greater than D

5. Number of slots required E


To me , i think, while 1,2,3,5 can be configurable, D will be automatically obtained as if a user will fix all 5, there are chances that equation will not solve.


Regards,
 
Hi Kuldeep ,


This may or may not meet your requirement ; can you check out this file ?


http://speedy.sh/A5BKW/KPJ-Book2-3.xlsx


Narayan
 
Thanks Narayan,


You made my day... in first go this meet what i wanted for.


To automate the slot value i just change two things


Lower Limit of Slot Size i converted the value to
Code:
=INT(Period/Number_of_Slots)

&

Upper Limit of Slot Size to =INT(Period/Number_of_Slots)+1


And everything worked. Hats off to you. Now i will start to understand how did you achieve that.


Regards,
 
Back
Top