CODEWITHSUNDEEP
vbaexcel
user3438716
user3438716

Reputation: 35

Excel Macro to separate date into year month week

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?

Upvotes: 1

Views: 5528

Answers (4)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

  • 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

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96781

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)

Upvotes: 1

Setsuna F. Seiei
Setsuna F. Seiei

Reputation: 302

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'

Upvotes: 0

Michael Moulsdale
Michael Moulsdale

Reputation: 1488

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

Upvotes: 1

Related Questions