data conversion from month to weeks

Hi all!

I am working on this "maintenance schedule" that is currently sorted out into tabs that are organized into the 12 months, jan, feb and so on.

I have to write either a command or a program that would be efficient enough to easily convert these monthly data in weekly ones.

So the command would have to convert those "jan, feb, mar... ...dec" tabs into "week 1, week 2, week 3... ...week 52". (prefably by linking them up some how)

Does anyone have any suggestions on how i go about doing this?

Originally Posted by Bryan Hessey

Hi,

there may be another way, but

=INT((A1-DATEVALUE("1/1/"&YEAR(A1)))/7)+1

will convert a date to a Week number

Did you mean to breakdown the 12 monthly sheets to 52 weekly ones? - or just start on 52 weekly ones for new data?

hth
---

not converting date to week number simply. But rather... ...

Yes breaking down with data i already have that is classified into months!

See i have "month worksheets" now with the schedules of the 12 individuals months in those worksheets.

so i have to write a command that will automatically convert these 12 months into 52 weeks for a whole year. The new "week worksheets" should be in a new file that can be linked to the one with the "month worksheets".

Thanks for the efficient reply. Hope i clarified myself! =)

There is maybe one issue that might affect this. If not, then sorry for intruding!

You may need to think about how the months are going to be broken down.

If every month was 28 days long, it would be easy, ie: 4 weeks per month.

But, of course, it isn't like that. For example, for January, there are 31 days. So, would January have 4 weeks or 5? You need to establish this before going further.

Say, for example, January 1st is a Monday, and your "week" runs from Monday to Sunday.

So:

Week 1 would be: January 1st to 7th
Week 2 would be: January 8th to 14th
Week 3 would be: January 15th to 21st
Week 4 would be: January 22nd to 28th

That leaves 29th, 30th, and 31st of January in week 5, together with February 1st - 4th.

So, which "month" would week 5 fit into? Would it be January or February? This could have an impact on what you are trying to achieve.

Originally Posted by Cumberland

There is maybe one issue that might affect this. If not, then sorry for intruding!

You may need to think about how the months are going to be broken down.

If every month was 28 days long, it would be easy, ie: 4 weeks per month.

But, of course, it isn't like that. For example, for January, there are 31 days. So, would January have 4 weeks or 5? You need to establish this before going further.

Say, for example, January 1st is a Monday, and your "week" runs from Monday to Sunday.

So:

Week 1 would be: January 1st to 7th
Week 2 would be: January 8th to 14th
Week 3 would be: January 15th to 21st
Week 4 would be: January 22nd to 28th

That leaves 29th, 30th, and 31st of January in week 5, together with February 1st - 4th.

So, which "month" would week 5 fit into? Would it be January or February? This could have an impact on what you are trying to achieve.

hey no problem at all!

Week 1 would be: January 1st to 7th
Week 2 would be: January 8th to 14th
Week 3 would be: January 15th to 21st
Week 4 would be: January 22nd to 28th

and week 5 would be from 29 jan to 4 feb =) (like you said)
week 6: 5 feb to 11 feb ...
.... week 9: 26 feb to 4 mar and so on

hence week 5 would be a combination of some jan and feb days, which would also be the case for other weeks in the year too, e.g. week 9 =)

hence it should not matter whether how many days each month has

** i intend for the weeks to start on a monday too!

so how do i go about this? how do i write the command? would in be in the original file with the months? or do i have to first create a file with 52 weeks?

Originally Posted by Bryan Hessey

Hi,

What will be the basis for 'breaking down' the current data to assess which sheet it will go to ?

---

the basis would be using the allocating of the 12 months in a year into the standard 52 or 53 weeks we have in year. like what cumberland said! =) i believe that excel can already recognize which weeks the days in a year/month fall into by the usage of the command "weeknum"

so as i explain earlier on i already have 12 worksheets (sorted by months) in a file which need to become 52 or 53 worksheets (sorted by weeks) in a second file now

and its quite tedious so simply "copying and pasting" is ruled out =)

can i send anyone an email of my file because this forum doesn't allow excel files to be uploaded.

i uploaded an image on this forum:

http://www.computerhope.com/cgi-bin/...m=1163665599/0