Macro in Excel

Hello Experts,

I have a excel file as like below:

 Original File
I want to write a macro which will take the input from "E1" column i.e. 2014 and get the Quarter ,Month, week in the below manner :

Expected file
After making this it should delete the data from "E1" column.

For Week it should take only 7 days and continue like "Week 1" Week 2 .....and so on till 31st Dec of the year which is present on E1 column.  The month field will have the name of the month and should merge the cells of that months.

Attached here with the excel for how wanted to be done using macro.
LVL 17
Swadhin Ray Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Will all weeks be seven-day periods beginning with January 1-7 of the calendar year?  If so, where does December 31 go?  It is an extra day.  In leap years, Dec. 30 & 31 are extra.
Swadhin Ray Author Commented:
Hello Ray,

Based on the year input in  "E1" column i.e. 2014 [as per my example or any other year ].
Here is the logic what I am looking for :

condition 1
1) check is the "E1" column is a leap year or not .
2) if yes the do not delete the "BL" column in first sheet.
  The assign the dates to the columns till "GD" column in first sheet
 Then assign the dates to the second sheet which will be similar to my first sheet .
3) else delete the  BL which is ( FEB 29th) as per the data in the first sheet.  
Once deleted then last column will be till "GC" , so now assign the dates till "GC" column in first sheet and put the dates into second sheet.  

condition 2
Assign weeks starting from  1st Jan accommodating  7 days a week....  

This is what I am looking for.

Here is the sample file with what I will get from the application always have two sheets 1 and 2:
Rob HensonFinance AnalystCommented:
You can actually do this with formulas and conditional formatting rather than VBA.

Do you want to me provide an example or are you set on VBA route.

Rob H
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Rob HensonFinance AnalystCommented:
Do the dates in row 6 have to be true dates so that you can use them in other calculations?

If not, then it doesn't matter what the value of E1 is as the dates with the formatting that you are showing will essentially not change. Therefore you could set it up with dates from any year, grouped and formatted as required and then leave it, except for an adjustment every four years to allow for 29 Feb; this could be a hidden column that gets unhidden every four years.

Rob H
Swadhin Ray Author Commented:
Yes it should be true dates based on the input from E1 column. And I dont want to hide , I want to delete that column.
So that I can use it as a final report. The only option I am looking for VBA macro.
Rob HensonFinance AnalystCommented:
Dates can easily be calculated from the input cell.

=DATE(E1,1,1)    will give 1 Jan of year specified.

Assuming 1 Jan in E6, the following in F6 will give 2 Jan

=E6+1   copy across row 6 will increment dates.

Week can then be determined by (row 6 date less 1 Jan)/7


=TEXT(E6,"mmm")    copied across.

Quarter, like week but divided by 91 (7 x 13).

With conditional formatting you can make the repeated values disappear. Don't think you can merge & centre though.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Swadhin Ray Author Commented:
Why I need VB because we have to make the formatting in second sheet too.
And also need to merge it.

So it would be great if you can provide me some sample for the given sheet.
Swadhin Ray Author Commented:
Glenn RayExcel VBA DeveloperCommented:
Hi slobaray,

Since you've accepted a non-VBA solution for this question, please either post a new question specifically requesting a VBA solution or re-open this one.

VBA will be the only way to create/apply the merged cells for the weeks, months, and quarters

With Thanks,
Rob HensonFinance AnalystCommented:
Correct me if I am wrong but I do not see that the formatting or merge settings will have to change once set up for the first time.

The dates will always start at 1 Jan so week 1 will always be the first 7 columns, week 2 the next 7 etc etc
Quarter 1 will always be the first 13 weeks, Quarter 2 the next 13 etc
Number of days in the Month will always be the same, except every 4 years when Feb has an extra day.

To overcome the Leap year issue, you could set up two copies of the required range and format with merge cells, one  accommodating 29 Feb and the other not. You can then just copy and paste formatting. One action required at start of Leap Year, one action required at start of next year and then leave alone for 3 years.

Rob H
Swadhin Ray Author Commented:
What I have done is I pulled the data as 29th Feb by default from my SQL which generates the data and put the year in E1 column .

Now what I did is checked if the year is a leap year or not , if not then delete the 29th day of feb. and then generate the dates , and then based on the dates generate the week, month ,quarter [Which will always start with 1 and from 1st JAN ], now accoumating till 7 days this will move on .,... .

Based on this I solved the problem. But I am still facing the issue on merging the cells in VBA code ,,.. Which I am still working on that.. But i know if I failed then I am sure I will open another question to get the help..

But the solution what was provided was also working only problem was the SQL which was generating the data was little hard coded for which I cannot use the provided solution here but in my another issue this was well utilized and we all are happy to use Expert exchange which holds the great experts together  :-) ....

Thanks a lot experts.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.