Convert financial week into corresponding month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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
 
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.
 
Try this!

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

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

And so on.
 
Back
Top