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

Months to weeks dates breakdown along with associated data.

V

VinRed

New Member
Hi
I am working on forecast model which basically breaks down monthly forecast into weekly. While breaking it down, main thing to consider is all the holidays mention in the attached calender and related changes in week start date.
Can you please help me with what formula I should break this into weekly dates format and modify related changes in the data?
So far I have been entering this dates manually but since this weekly breakdown of the forecast has to be done till 6 months, this becomes very tedious.

Any help is deeply appreciated.

Thanks
 

Attachments

  • months to weeks.xlsx
    21.9 KB · Views: 5
Hey Narayan
Thanks for your reply, and yeah my bad, I should have include formula location in my sheet.
But anyways here I have modified the sheet.
Just fyi to sort out the date parts I have so far broken down months into weekly start date at same time I have listed all the holidays in one column. I am using
=IFERROR(IF(VLOOKUP(D4,$O$4:$O$16,1,FALSE),D4+1,D4),D4) to check if any week start date is mathhing with holiday date, and if it does it should increase it to next date.
One problem with this is that I am not able figure out how to run this in a loop, so that if there are two back to back holidays it should pick up the next working day and so forth.

In same attached spreadsheet, Date worksheet should hopefully explain it better than what I have written here.

Thanks again
 

Attachments

  • months to weeks (1).xlsx
    23.5 KB · Views: 3
NARAYANK991 said:
Hi ,

Can you check your file now ?

Narayan
Click to expand...
Hey Narayan
Thanks! that definitely worked. Only problem that I am facing now is I am not able to use that formula in any other sheet or any other cell for that matter, it gives me error. I am not that good with excel formulas so this may sound little silly but is it cell specific formula? I didn't understand use of column A in this formula either.

Thanks for you help!
 
Hi ,

I do not think that there should be any problem like that.

The formula needs only two inputs : an input date , and a list of holidays.

It is an array formula , and would need to be entered using CTRL SHIFT ENTER.

If you can give the reference addresses of :

1. The range where you have listed your holidays

2. The range where you wish to introduce this formula

Also specify whether you need to copy the formula downwards or across.

The only constraint , which I did not mention , is that the input dates have to be within a calendar year ; thus if your input dates go from April 2014 till March 2015 , then the formula would need to be modified when it is copied across years.

Narayan
 
NARAYANK991 said:
Hi ,

I do not think that there should be any problem like that.

The formula needs only two inputs : an input date , and a list of holidays.

It is an array formula , and would need to be entered using CTRL SHIFT ENTER.

If you can give the reference addresses of :

1. The range where you have listed your holidays

2. The range where you wish to introduce this formula

Also specify whether you need to copy the formula downwards or across.

The only constraint , which I did not mention , is that the input dates have to be within a calendar year ; thus if your input dates go from April 2014 till March 2015 , then the formula would need to be modified when it is copied across years.

Narayan
Click to expand...
Hey Narayan
Thanks for your reply! As you said I spent quite amount of time figuring out why exactly I am not able to copy this formula to other sheet. Still I am only able to get regular week dates right, other date which match up with holiday table are creating errors. I have attached the sheet here. Could please let me know if I am doing something wrong?
Also formula is working fine for other dates other than holiday, even if it is from different calender year.
 

Attachments

  • Updated months to week.xlsx
    12.5 KB · Views: 1
Hi ,

I think the problem was that the formula had not been array-entered , using CTRL SHIFT ENTER.

See your file now.

For better readability , I have created a named range HolidaysList which refers to the dates in column M.

Narayan
 

Attachments

  • Updated months to week.xlsx
    12.6 KB · Views: 2
NARAYANK991 said:
Hi ,

I think the problem was that the formula had not been array-entered , using CTRL SHIFT ENTER.

See your file now.

For better readability , I have created a named range HolidaysList which refers to the dates in column M.

Narayan
Click to expand...
oh boy! I am really stuck with this one. So as per your suggestion i tried using array entry in the columns but for some reason I am not able to change the dates for each row, instead I am getting same dates as first column. Since its array based I cant even change or drag individual cells. I tried using range instead of one column for eg. C2:C32 instead of C2 but still the same result (as shown in the sheet). I think I really dont know anything about this function it seems :(

I really appreciate your time with this one man!
thanks
 

Attachments

  • Arry Entry.xlsx
    12.3 KB · Views: 1
Hi ,

See your file now.

There are two types of array formulae ; one where you enter the formula in a single cell , and press CTRL SHIFT ENTER to complete entry. The other is where you select a multiple-cell range , enter the formula , and press CTRL SHIFT ENTER to complete entry ; in this case , when you do that , the formula will populate all the cells in the range with the same formula , but if the formula has been written correctly for this type of entry , then the values in each cell will be the appropriately calculated.

The formula in your case is of the first type , which is why when you enter it in the fashion meant for the second type , all the cells have the same value.

Narayan
 

Attachments

  • Arry Entry.xlsx
    13.1 KB · Views: 3
NARAYANK991 said:
Hi ,

See your file now.

There are two types of array formulae ; one where you enter the formula in a single cell , and press CTRL SHIFT ENTER to complete entry. The other is where you select a multiple-cell range , enter the formula , and press CTRL SHIFT ENTER to complete entry ; in this case , when you do that , the formula will populate all the cells in the range with the same formula , but if the formula has been written correctly for this type of entry , then the values in each cell will be the appropriately calculated.

The formula in your case is of the first type , which is why when you enter it in the fashion meant for the second type , all the cells have the same value.

Narayan
Click to expand...
Yup! found my mistake. Thank you very much buddy for your help! have a good one!
 
Hi ,

Obviously , you cannot use this formula as it is , in VBA.

Can you post the code in which you want to use this formula ?

Narayan
 
NARAYANK991 said:
Hi ,

Obviously , you cannot use this formula as it is , in VBA.

Can you post the code in which you want to use this formula ?

Narayan
Click to expand...
yeah it kinda requires selection.FormulaArray function, I tried using that but still gives error.


here is the part of the code where I am using this formula

Range("E2").Select
Selection.FormulaArray = _
"=IF(ISNUMBER(MATCH(RC[-2],R2C13:R14C13,0)),MIN(IF((MMULT(--(ROW(INDEX(C1,DATE(YEAR(RC[-2]),1,1)):INDEX(C1,DATE(YEAR(RC[-2]),12,31)))=TRANSPOSE(R2C13:R14C13)),ROW(R2C13:R14C13)^0)=0)*(ROW(INDEX(C1,DATE(YEAR(RC[-2]),1,1)):INDEX(C1,DATE(YEAR(RC[-2]),12,31))))>RC[-2],(MMULT(--(ROW(INDEX(C1,DATE(YEAR(RC[-2]),1,1)):INDEX(C1,DATE(YEAR(RC[-2]),12,31)))=TRANSPOSE(R2C13:R14C1" & _
"R2C13:R14C13)^0)=0)*(ROW(INDEX(C1,DATE(YEAR(RC[-2]),1,1)):INDEX(C1,DATE(YEAR(RC[-2]),12,31)))))),RC[-2])"
Selection.AutoFill Destination:=Range("E2:E32")

Range("E2:E32").Select



Its basically same formula only needs to be specified in R1C1 but I dont know where excatly its going wrong!
 
Hi ,

I doubt that it will be possible ; the easier way may be to use a helper column , and simplify the formula.

The reason for the error is not because of the formula itself , but because in VBA , the limit for the formula length is 255 characters ; this formula is more than twice as long.

The only way to accommodate longer formulae in VBA is to split the formula into two constituent parts , so that each formula segment is less than 256 characters in length , and then use the REPLACE function to insert the second formula segment into the first ; you can see a thread on this here :

http://chandoo.org/forum/threads/vba-long-array-formula.15957/

The problem with adopting this technique is that each of the two segments should evaluate to valid Excel formulae , which in your case appears to be a difficult proposition.

Narayan
 
NARAYANK991 said:
Hi ,

I doubt that it will be possible ; the easier way may be to use a helper column , and simplify the formula.

The reason for the error is not because of the formula itself , but because in VBA , the limit for the formula length is 255 characters ; this formula is more than twice as long.

The only way to accommodate longer formulae in VBA is to split the formula into two constituent parts , so that each formula segment is less than 256 characters in length , and then use the REPLACE function to insert the second formula segment into the first ; you can see a thread on this here :

http://chandoo.org/forum/threads/vba-long-array-formula.15957/

The problem with adopting this technique is that each of the two segments should evaluate to valid Excel formulae , which in your case appears to be a difficult proposition.

Narayan
Click to expand...
Oh yeah! its kinda difficult to split the formula in this case. Looks like I am gonna have to start from scratch to build this one again right? :(
Thanks you for your time though! really appreciate it.
 
Hey Narayan
So I have been trying to break your formula into multiple stages but I am not able to get the same result as whole. Since you started with development of this formulacould please help me figuring out where exactly I am going wrong.
Thanks
I have attached the file for your reference.
 

Attachments

  • BreakUp formula.xlsx
    18.5 KB · Views: 2
Hi ,

I doubt that you can try and break down the formula in individual cells / columns like this ; if you wish to use helper cells / columns , then I am sure there may be easier ways to get the same result.

The reason you cannot do this is because the MMULT function is returning an array ; when you enter this in a single cell , say H2 , and in another column cell , say I2 , you use the formula =IF(H2>.....) , what will be compared is a single value rather than an array.

If you can use helper cells , then the logic will have to be different , so that at each stage we work with a single value.

Narayan
 
NARAYANK991 said:
Hi ,

I doubt that you can try and break down the formula in individual cells / columns like this ; if you wish to use helper cells / columns , then I am sure there may be easier ways to get the same result.

The reason you cannot do this is because the MMULT function is returning an array ; when you enter this in a single cell , say H2 , and in another column cell , say I2 , you use the formula =IF(H2>.....) , what will be compared is a single value rather than an array.

If you can use helper cells , then the logic will have to be different , so that at each stage we work with a single value.

Narayan
Click to expand...
Hey
Sorry but I didn't understand what helper cells/column are you talking about and how exactly use them to get the similar results?
Could you please give an example for any single value from my sheet?

Thanks
 
Hello VinRed,

I did not go through all post. If you are looking to return any start date without including holidays, try this formula with just ENTER

=C2+MATCH(1,ISNA(MATCH(C2+{0,1,2,3,4,5,6},M$2:M$14,0))+0,0)-1
 
Last edited:
You must log in or register to reply here.
Back
Top