Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
RichHoughton
 
Posts: n/a
Default Convert financial week into corresponding month

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich
  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Mon, 23 May 2005 07:52:21 -0700, RichHoughton
wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich


Two questions:

1. How do you define your financial week? (i.e. when does financial week 1
start for any given year?

2. What do you want to show if the week in question spans two months?

In general, one could compute a date by computing Week 1; Day 1 and then adding
(weeknum-1) * 7 to that date. But since there are not exactly 52*7 days in a
year, we need to know your convention for treating the extra days.

There are various methods used, and sometimes there will be a week 53 or a week
1 which is less than seven days long.


--ron
  #3   Report Post  
Barb R.
 
Posts: n/a
Default

Could you provide the Month and WW correlation?

WW1-4 April
5-8 May
9-13 June

Or whatever it is. This could be done with a lookup chart or an equation.

"RichHoughton" wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich

  #4   Report Post  
Barb R.
 
Posts: n/a
Default

I've also seen WW00.

"Ron Rosenfeld" wrote:

On Mon, 23 May 2005 07:52:21 -0700, RichHoughton
wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich


Two questions:

1. How do you define your financial week? (i.e. when does financial week 1
start for any given year?

2. What do you want to show if the week in question spans two months?

In general, one could compute a date by computing Week 1; Day 1 and then adding
(weeknum-1) * 7 to that date. But since there are not exactly 52*7 days in a
year, we need to know your convention for treating the extra days.

There are various methods used, and sometimes there will be a week 53 or a week
1 which is less than seven days long.


--ron

  #5   Report Post  
RichHoughton
 
Posts: n/a
Default

Financial year = April - March

I would therefore have Financial Week 1 being the week that houses April
1st. This needs to be a week day, i.e. if April 1st falls on a Saturday,
Financial Week will start from the following Monday. Clear as mud i know!!

I'm beginning to think a lookup table would be the easiest option. Yes
there would be instances of a 53 Financial week year. 2004/2005 was for
instance.

It would be cooler if an equation could do this.

Thanks all for your help so far.

Rich

"Barb R." wrote:

Could you provide the Month and WW correlation?

WW1-4 April
5-8 May
9-13 June

Or whatever it is. This could be done with a lookup chart or an equation.

"RichHoughton" wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich



  #6   Report Post  
swatsp0p
 
Posts: n/a
Default

This may work for you:

Insert a new column A and in A1, enter April 3, 2005.
In A2 enter =A1+7 and copy down for 52 weeks
In B1 enter: =IF(WEEKNUM(A1,1)-130,WEEKNUM(A1,1)-13,WEEKNUM(A1,1)+39)
(format as General)
In C1 enter =DATE(YEAR(A1),MONTH(A1),DAY(A1)) (format as Custom, mmmm)

copy B1:C1 down for the 52 weeks.

Hide column A if desired.

Column B is your week number and column C is the month name

NOTE: The weeknumber assumes your week begins on Sunday and week #1 began
Jan 2, 2005. To begin on Monday, WEEKNUM(A1,2) will do that. If Week 1 began
Dec. 26 or 27, 2004, adjust the -14 to -13 and the +39 to +40

HTH

--
The older I get, the better I used to be.


"RichHoughton" wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich

  #7   Report Post  
Barb R.
 
Posts: n/a
Default

Are your work weeks from Sun-Sat? If not, what are they?

"RichHoughton" wrote:

Financial year = April - March

I would therefore have Financial Week 1 being the week that houses April
1st. This needs to be a week day, i.e. if April 1st falls on a Saturday,
Financial Week will start from the following Monday. Clear as mud i know!!

I'm beginning to think a lookup table would be the easiest option. Yes
there would be instances of a 53 Financial week year. 2004/2005 was for
instance.

It would be cooler if an equation could do this.

Thanks all for your help so far.

Rich

"Barb R." wrote:

Could you provide the Month and WW correlation?

WW1-4 April
5-8 May
9-13 June

Or whatever it is. This could be done with a lookup chart or an equation.

"RichHoughton" wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich

  #8   Report Post  
Barb R.
 
Posts: n/a
Default

Try this!

A1 = YEAR B1 = 2005
A2 =
IF(WEEKDAY(DATE(B1,4,1),1)=7,DATE(B1,4,1)+1,DATE(B 1,4,1)-WEEKDAY(DATE(B1,4,1),1)+1)

B2 = 1
A3 = A2+7 B3 = B2+1

And so on.

"RichHoughton" wrote:

Financial year = April - March

I would therefore have Financial Week 1 being the week that houses April
1st. This needs to be a week day, i.e. if April 1st falls on a Saturday,
Financial Week will start from the following Monday. Clear as mud i know!!

I'm beginning to think a lookup table would be the easiest option. Yes
there would be instances of a 53 Financial week year. 2004/2005 was for
instance.

It would be cooler if an equation could do this.

Thanks all for your help so far.

Rich

"Barb R." wrote:

Could you provide the Month and WW correlation?

WW1-4 April
5-8 May
9-13 June

Or whatever it is. This could be done with a lookup chart or an equation.

"RichHoughton" wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich

  #9   Report Post  
Barb R.
 
Posts: n/a
Default

Add the following in C2 and copy down

C2 = A2+6 (format to display month)

"Barb R." wrote:

Try this!

A1 = YEAR B1 = 2005
A2 =
IF(WEEKDAY(DATE(B1,4,1),1)=7,DATE(B1,4,1)+1,DATE(B 1,4,1)-WEEKDAY(DATE(B1,4,1),1)+1)

B2 = 1
A3 = A2+7 B3 = B2+1

And so on.

"RichHoughton" wrote:

Financial year = April - March

I would therefore have Financial Week 1 being the week that houses April
1st. This needs to be a week day, i.e. if April 1st falls on a Saturday,
Financial Week will start from the following Monday. Clear as mud i know!!

I'm beginning to think a lookup table would be the easiest option. Yes
there would be instances of a 53 Financial week year. 2004/2005 was for
instance.

It would be cooler if an equation could do this.

Thanks all for your help so far.

Rich

"Barb R." wrote:

Could you provide the Month and WW correlation?

WW1-4 April
5-8 May
9-13 June

Or whatever it is. This could be done with a lookup chart or an equation.

"RichHoughton" wrote:

Hi

Probably a really easy one, but has me scratching my head. I have a column
of numbers (week numbers) for the new financial year. What i would like to
do is put in some formula or code or something in the adjoining cell that
picks up the week number in the first cell and churns out the month, i.e. if
1 was in cell A1, April would be found in B1. It's not quite as easy as
putting 4 weeks per month as some months are longer than others, but i still
dunno. Needs to work on the date of the weeks i think.

Any help??

Many thanks

Rich

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to get week number in month in excel ? ikin Charts and Charting in Excel 2 January 16th 05 05:54 PM
Formula to convert to month Mel Excel Worksheet Functions 2 December 1st 04 11:52 PM
calculate month from week number ankman Excel Worksheet Functions 2 November 24th 04 01:27 AM
Convert week number into calendar month? WickyWick Excel Worksheet Functions 2 November 9th 04 09:01 PM
Returning the Week Number of a Specific Date on a Month arjcvg Excel Worksheet Functions 1 November 3rd 04 04:35 AM


All times are GMT +1. The time now is 04:36 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"