Macro in Excel
Hello Experts,
I have a excel file as like below:
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 :
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.
Sample-File.xls
I have a excel file as like below:
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 :
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.
Sample-File.xls
Glenn Ray
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.
ASKER
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:
Final-Sample.xls
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:
Final-Sample.xls
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.
Thanks
Rob H
Do you want to me provide an example or are you set on VBA route.
Thanks
Rob H
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.
Thanks
Rob H
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.
Thanks
Rob H
ASKER
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.
So that I can use it as a final report. The only option I am looking for VBA macro.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
And also need to merge it.
So it would be great if you can provide me some sample for the given sheet.
ASKER
thanks
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,
-Glenn
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,
-Glenn
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.
Thanks
Rob H
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.
Thanks
Rob H
ASKER
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.
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.