trying to represent a date as yyyy-ww

March 11, 2014 at 11:53:29
Specs: Windows XP
need a formula to represent a date value as year nad week. Week must begin on Friday and include leading zeros.
i.e. 01/03/2014 represented as 2014-01

See More: trying to represent a date as yyyy-ww

Report •

#1
March 11, 2014 at 12:20:03
Try this:

=IF(LEN(WEEKNUM(A1,15))=1,TEXT(A1,"yyyy")&"-0"&WEEKNUM(A1,15),TEXT(A1,"yyyy")&"-"&WEEKNUM(A1,15))

Let us know if it works.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 11, 2014 at 12:34:05
I have added the formula to column e in test excel worksheet, colum a contains dates value in cell a1 represented as 1/3/2014. I get #NUM! error.
Idealy I'd like to create this converted value in Access 2007, but would/could live with it in Excel 2007.
thanks to DerbyDad03 for the reply

Report •

#3
March 11, 2014 at 13:23:32
Having the same problem.

Seems the Range_Type for the WEEKNUM() function is only accepting the numbers 1 or 2.

You will need Excel 2010 to use any of the other options numbers.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
March 11, 2014 at 13:30:53
Too bad...time to upgrade! ;-)

15 is the code to start a week on Friday in Excel 2010 and beyond. I can play with the formula a little later and see what can be done about the Friday issue, unless Mike comes up with something while I'm driving home.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
March 11, 2014 at 13:55:31
Didn't come up with my own formula,
but see this thread, looks like it is what you need:

http://www.mrexcel.com/forum/excel-...

MIKE

http://www.skeptic.com/


Report •

#6
March 11, 2014 at 14:36:36
I have a question based on your original post.

If 1/3/2014 is to be displayed as 2014-01, meaning Week 1 in the year 2014, what should 1/1/2014 and 1/2/2014 be displayed as?

Are they in Week 53 of 2013, even though they are actually in 2014?

That might be a tough formula to build since each year will have different dates in the first and/or last week of that year.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
March 12, 2014 at 05:39:53
Week 1 of 2014 begins December 27, 2014
Week 53 of 2014 begins December 26, 2014
Week 1 of 2015 begins Jan 2, 2015
there are normally 52 work weeks identified in each year


Report •

#8
March 12, 2014 at 08:27:23
This makes no sense, at least not to me.

Week 1 of 2014 begins December 27, 2014
Week 53 of 2014 begins December 26, 2014
Week 1 of 2015 begins Jan 2, 2015

The first criteria has to be wrong, since I really doubt you want Week 1 of any given year to start in December of that same year:

Week 1 of 2014 begins December 27, 2014

Do you mean this?

Week 1 of 2014 begins December 27, *2013*

Assuming you meant 2013, I am also confused by your criteria for when Week 1 begins in any given year.

Week 1 of 2014 begins December 27, 2013

Week 1 of 2015 begins Jan 2, 2015

Why would Week 1 of 2014 begin on the last Friday of 2013 yet Week 1 of 2015 begins on the first Friday in 2015? In other words, why doesn't Week 1 of 2015 begin on 12/26/2014?

What are you expecting for years beyond 2015 - the last Friday of the previous year or the first Friday of the current year?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
March 12, 2014 at 10:58:24
Try this:

Week One for 2014 is the first Two days of the year:


Wednesday, January 01, 2014 Week 1
 Thursday, January 02, 2014 Week 1

Week Two for 2014 starts on the First Friday,

Friday, January 03, 2014 Week 2
Friday, January 10, 2014 Week 3

The rest of the year, each week begins on Friday, which results
in you having 53 Weeks in 2014.

  Friday, December 26, 2014 Week 53
Saturday, December 27, 2014 Week 53
  Sunday, December 28, 2014 Week 53
  Monday, December 29, 2014 Week 53

If this is what you are looking for,
then in Cell A1 enter your Date
then in Cell B1 enter the formula: =WEEKNUM(A1+2)

For Today's Date: March 12, 2014 you should get 11

Now using a modifed version of DerbyDad03's formula you get:

=IF(LEN(WEEKNUM(A1+2))=1,TEXT(A1,"yyyy")&"-0"&WEEKNUM(A1+2),TEXT(A1,"yyyy")&"-"&WEEKNUM(A1+2))

See how that works for you.
It's not perfect, but.......

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#10
March 12, 2014 at 15:49:32
Thank you Mike & DerbyDad, works well for the current year. just for clarity what is the significance of the (A1+2) does the +2 have a relationship to the start of the week that the first date of the year falls on. for example the dates in the last week of December 2013 do not inherit week 01 correctly. When I apply the Code to the dates in 2015 and alter it to read (A1+1) the jan dates align correctly but the Dec 30 & 31 2014 are not in correct roll week. likewise the 30& 31 2013 are in the incorrect week.

Report •

#11
March 12, 2014 at 16:49:57
I'll answer your question about A1+2 if you'll answer my question from Response #8, specifically:

You said:

Week 1 of 2014 begins December 27, 2013
Week 1 of 2015 begins Jan 2, 2015

Why would Week 1 of 2014 begin on the last Friday of 2013 yet Week 1 of 2015 begins on the first Friday in 2015? In other words, why doesn't Week 1 of 2015 begin on 12/26/2014?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#12
March 12, 2014 at 16:59:43
Getting the transition between years is always a problem
and both the US and Europe do it differently.
Getting your week to begin on a Friday, just adds a bit more to it.

For more on calculating a WEEK NUMER see here:

http://www.cpearson.com/excel/WeekN...

The +2 simply fools the =WEEKNUM() function into thinking the date is two days prior to the actual day, and since the default option is to start the week on Sunday, the two days push it back to Friday.
If you use the Return_Type of 2, to start your week on Monday, then this formula also works: =WEEKNUM(A1+3,2)

Neither is in any way a perfect solution.

Also, a simpler and more direct approach, might be to use a Week Number table and do a =VLOOKUP()

It should only take a few moments to create a list of Dates in a Column and then assign a specific week number that begins on any weekday you like.
Something like:

               A                      B
 1) Wednesday, February 26, 2014      9
 2) Thursday, February 27, 2014       9
 3) Friday, February 28, 2014        10
 4) Saturday, March 01, 2014         10
 5) Sunday, March 02, 2014           10
 6) Monday, March 03, 2014           10
 7) Tuesday, March 04, 2014          10
 8) Wednesday, March 05, 2014        10
 9) Thursday, March 06, 2014         10
10) Friday, March 07, 2014           11
11) Saturday, March 08, 2014         11
12) Sunday, March 09, 2014           11
13) Monday, March 10, 2014           11
14) Tuesday, March 11, 2014          11
15) Wednesday, March 12, 2014        11
16) Thursday, March 13, 2014         11
17) Friday, March 14, 2014           12
18) Saturday, March 15, 2014         12
19) Sunday, March 16, 2014           12

Then it is simply a matter of looking up the date and returning the week number assigned.

MIKE

http://www.skeptic.com/


Report •

#13
March 13, 2014 at 13:17:31
See if this solution work for you.

The problem is always the first few days of the year,
preceding the first Friday of the current year.

This formula will take those first few days,
and assign them to week 52 of the previous year.

So the transition from 2013 to 2014 will look like:

               A                         b
 1) Thursday, December 26, 2013      2013-51
 2) Friday, December 27, 2013        2013-52
 3) Saturday, December 28, 2013      2013-52
 4) Sunday, December 29, 2013        2013-52
 5) Monday, December 30, 2013        2013-52
 6) Tuesday, December 31, 2013       2013-52

 7) Wednesday, January 01, 2014      2013-52
 8) Thursday, January 02, 2014       2013-52

 9) Friday, January 03, 2014         2014-1                  
10) Saturday, January 04, 2014       2014-1
11) Sunday, January 05, 2014         2014-1
12) Monday, January 06, 2014         2014-1
13) Tuesday, January 07, 2014        2014-1
14) Wednesday, January 08, 2014      2014-1
15) Thursday, January 09, 2014       2014-1
16) Friday, January 10, 2014         2014-2


The formula is a bit long, so you would probably be best to Copy/Paste
rather than try to enter it free hand.


=IF(ISERROR(LEFT(((DATEDIF((CEILING(DATE(YEAR(A1),1,1)-6,7)+6),A1,"d"))/7),2)+1),YEAR(A1)-1&"-"&52,YEAR(A1)&"-"&LEFT(((DATEDIF((CEILING(DATE(YEAR(A1),1,1)-6,7)+6),A1,"d"))/7),2)+1)

MIKE

http://www.skeptic.com/


Report •

#14
March 13, 2014 at 13:33:48
Hey Mike,

Until we get clarification as to what he wants done with the first few and last few days of the year, I think we are wasting our time.

The criteria posted in Response #7 is inconsistent. One year seems to have Week 1 start at the end of December, the next year has Week one start after January 1. If that is true - in other words, if it's going to change year by year - then I don't think anything other than a VLOOKUP with specific dates and assigned week numbers will work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#15
March 13, 2014 at 14:23:19
Hey guys thank you very much for your continued support, the situation is somewhat unique in the fact that weeks begin on Fridays, our production plans and employee pay ending/pay days are aligned with a Friday start day, for the few days at end of December, I will manually fix. The formulas and suggested VLookup are the best solutions that anyone has come up with including limited knowledge I have.

Report •

Ask Question