Excel Macro to separate date into year month week

1
votes

I am having a really difficult time with this excel table and was trying to use VBA as well as Excel functions to get the date formatted into Year, Month, Week but I keep running into issues.

I have the below input:enter image description here

The reason Column1 looks so odd is because I was TRYING to use =MONTH([Date]) but it looks all weird and doesn't sort into the month or week it is supposed to.

Below is the expected outcome if everything were to work.

enter image description here

So using the Date column I want to generate 3 columns: Year, Month, Week

Any idea if I am doing something wrong in excel or if there is an easy way to do this in VBA?

4
vbaexcel
you should chagne format of your column: content.gcflearnfree.org/topics/175/3_number_format_box.png – Dmitry Pavliv
Check the formatting on Column1, it might be that it is set to date, which would indeed format the number '12' to '01/12/1900'. It seems you are getting what you want, you just need to look for the excel formatting of your cells. Also update your question with the code you used so we can also take a look at that if necessary. – Yoh
@simoco thank you but now what can I to get the 3 columns? I still have to split this up so should I use VBA and use Split and then do some code to create the week number? – user3438716
what week number you need? week of year or week of month? – Dmitry Pavliv
Now my Date column is in the format 19-Dec-2013 so should I Split using VBA and do the rest of 3 columns myself? Week of month..Maybe using the Split function I would have 4 if statements since each month has 4 weeks and using the day I can classify which week it is of the month. Would there be an easier solution? – user3438716

4 Answers

1
votes

Not sure where the issue is but the following formulae will give you year, month, and day. Where a1 contains a date

=year(a1)
=month(a1)
=day(a1)

Of course if you want the month to show the month in words rather than a number you can format the cell as custom and then mmm

1
votes

with the date in A2 , in E2 enter:

=A2 and format as "yyyy"

In F2 enter:

=A2 and format as "mm-mmmm"

In G2 enter:

="Week " & ROUNDUP(DAY(A2)/7,0)
0
votes

You are using the right formula the only thing amiss is your column formatting as it is correctly pointed out in the comments. Follow these steps to get the desired formatting

  1. Select the column and right click Click 'Format Cell'
  2. In the'Number' tab select 'Custom' in the category list
  3. Enter 'mm - mmmm' as the type.

You should get '12 - December'

0
votes
  • Formula for year: =YEAR([@Date])
  • Formula for month: =MONTH([@Date]) & "-" & TEXT([@Date],"mmmm")
  • Formula for week: ="Week " & WEEKNUM([@Date],2)-WEEKNUM(EOMONTH([@Date],-1)+1,2)+1

and use appropriate format for each column.

enter image description here