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

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

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

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

message edited by mmcconaghy

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.

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

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.

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

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 ofthat same year:Week 1 of 2014 begins December 27, 2014Do 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, 2015Why 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.

Try this: Week

Onefor 2014 is the firstTwodays 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 53If 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

message edited by mmcconaghy

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.

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, 2015Why 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

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 12Then it is simply a matter of looking up the date and returning the week number assigned.

MIKE

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 thepreviousyear.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

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

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.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History