data conversion from month to weeks

[lalala]

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?

[Bryan Hessey]

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?

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

[lalala]

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! =)

[Bryan Hessey]

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! =)

Hi,

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

---

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

[lalala]

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?

[lalala]

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.

[lalala]

i uploaded an image on this forum:

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

[Bryan Hessey]

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.

Hi,

as per my first post, you can decide a week number for a specific date, your further requirements (begin on Monday) sets the formula to

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

OR you can use the Weeknum function from the Analysis Toolpak.

But, what happens to the 1/1/2006?

It is a Sunday, and your week starts Monday, so is 2/1/2006 = week 2?

Your pic does not show whether your dates are real dates displayed day & month, or text items that are date-look-alike.

To upload a portion of your workbook, copy to a new sheet and remove most of the data, then in Explore (My Computer) rightmouse the file and Add To Archive, rename as a .zip

Which date would you chose to determine the 'week' for the row? obviously neither of the actual dates, planned start? planned completion? or do you have a 'received order' date somewhere?

Which column of your worksheets will always have an entry? Column B from row 3 onwards?

What is the significannce of the 'I/E' in A7 ?

Any further points?
---

note, VB code to do the move is not difficult, deciding where to go is.

[lalala]

where do i add a row to use the "=INT((A1+7-WEEKDAY(A1,3)-DATEVALUE("1/1/"&YEAR(A1)))/7)+1" command? DO i need a new worksheet?

i think i'll consider 2 jan as week 1 and so on.

i don't quite understand this "Your pic does not show whether your dates are real dates displayed day & month, or text items that are date-look-alike.
"

"Which date would you chose to determine the 'week' for the row? obviously neither of the actual dates, planned start? planned completion? or do you have a 'received order' date somewhere?"

i would use the planned start.

[lalala]

Hi,

Which column of your worksheets will always have an entry? Column B from row 3 onwards?

What is the significannce of the 'I/E' in A7 ?

Any further points?
---

note, VB code to do the move is not difficult, deciding where to go is.

nearly all the columns will have entries if that's what you are asking because column B is the job that needs to be done and the related details are in columns C, D and so on.

I/E is one of the subdivisions in my department. im in the maintenance department. metal, machinery are the other sub divisions too.

[Bryan Hessey]

where do i add a row to use the "=INT((A1+7-WEEKDAY(A1,3)-DATEVALUE("1/1/"&YEAR(A1)))/7)+1" command? DO i need a new worksheet?

i think i'll consider 2 jan as week 1 and so on.

i don't quite understand this "Your pic does not show whether your dates are real dates displayed day & month, or text items that are date-look-alike.
"

"Which date would you chose to determine the 'week' for the row? obviously neither of the actual dates, planned start? planned completion? or do you have a 'received order' date somewhere?"

i would use the planned start.

Hi,

In your data, 'Blending', 'CAS' and 'PIBSA' etc have no dates to determine which sheet (if any) they should go to.

Where do you want the blank lines (they also are not dated)

Where do 'postpone' go?

What sort of date is 'SD NOV', 'Wait for Planner', 'Blender & CAS sharing this pump' etc ?

It may be difficult to determine which sheet to move many items unless a valid date can be used.

Any further clues?

----

[Bryan Hessey]

where do i add a row to use the "=INT((A1+7-WEEKDAY(A1,3)-DATEVALUE("1/1/"&YEAR(A1)))/7)+1" command? DO i need a new worksheet?

i think i'll consider 2 jan as week 1 and so on.

So 1/1/2006 is week zero?

i don't quite understand this "Your pic does not show whether your dates are real dates displayed day & month, or text items that are date-look-alike.
"

see your 'pretend dates' in Jun F25:F39

"Which date would you chose to determine the 'week' for the row? obviously neither of the actual dates, planned start? planned completion? or do you have a 'received order' date somewhere?"

i would use the planned start.

[lalala]

Hi,

In your data, 'Blending', 'CAS' and 'PIBSA' etc have no dates to determine which sheet (if any) they should go to.

Where do you want the blank lines (they also are not dated)

Where do 'postpone' go?

What sort of date is 'SD NOV', 'Wait for Planner', 'Blender & CAS sharing this pump' etc ?

It may be difficult to determine which sheet to move many items unless a valid date can be used.

Any further clues?

----

pibsa, cas, blending are the headings of the items under them e.g "PM 18M WO3012 Weighcell ISO Calibration"... so the dates are next to the items "PM 18M WO3012 Weighcell ISO Calibration" and so on.

can my blank lines still fall in between those data they are currently in between?

the "postpone" in each month would go into the last week of those very months they are classified under

im not sure about this "What sort of date is 'SD NOV', 'Wait for Planner', 'Blender & CAS sharing this pump' etc ?" for the moment. would it be possible that i work with those that have the dates first? for those without dates, can i temporarily assume them to be under the last week of each month?

Sorry. Not much clues i can offer as i'm still checking out the file, and i'm really an amatuer at excel! I'm so sorry bout that!

[lalala]

So 1/1/2006 is week zero?
see your 'pretend dates' in Jun F25:F39

yep 1 jan 2006 can be considered week 0... hmmm the some of the dates in jun are not typed in the same way! sorry bout that but this is really the original file i was given. sorry too guys that the data is really quite messy!

[Bryan Hessey]

yep 1 jan 2006 can be considered week 0... hmmm the some of the dates in jun are not typed in the same way! sorry bout that but this is really the original file i was given. sorry too guys that the data is really quite messy!

Hi,

yes, 'messy' is correct.

however, try the attached, CTRL/Shift/O to run the macro

"hmmm the some of the dates in jun are not typed in the same way" - - no, they are not dates.

I am away for most of the next two days, but will pop in for short looks-see as available.

Cheers
---

[lalala]

ok thanks for the moment... i may not reply during the weekend too =)

[Bryan Hessey]

ok thanks for the moment... i may not reply during the weekend too =)

Hi,

OK - - just run test on the file, and work out any date fiddles you need, but some may require you to amend your data, asin the Jun dates.

Column Y - Y1 is the first day of the Month of the sheet,
follows down the column, if a valid this year date in F, use that, otherwise use previous row.

Z3 onwards calculates the week for the specified row.

You may need to remove columns Y and Z after the split, or I can modify the macro to clear Y & Z in the new sheets.

Good luck
---

[lalala]

hey it reallys look great that it sorts out into 52 weeks after using ctrl+shift+o but the data is the columns is incorrect for some reason? and they are sorted out in weeks but the few different types of dates namely the planned/actual start/completion dates are not there too.

please tell me where i can amend the data.. which sheet and which column? Im really am an amatuer! =X

[Bryan Hessey]

hey it reallys look great that it sorts out into 52 weeks after using ctrl+shift+o but the data is the columns is incorrect for some reason? and they are sorted out in weeks but the few different types of dates namely the planned/actual start/completion dates are not there too.

?

in which specific sheet/row ?

===

[lalala]

check out sheet 48... the data sheets to have been moved 3 rows to the right... "Yes" should be under "required ops windows" and further down in row 33 the data seems to be messed up... cell E40 should not read
"2". same goes for sheet 44, 39...

in sheets 31 to 35 there are dates under "functional location" which should not be the case


that's what i spotted so far, i think the other sheets should be fine =)

[Bryan Hessey]

check out sheet 48... the data sheets to have been moved 3 rows to the right... "Yes" should be under "required ops windows" and further down in row 33 the data seems to be messed up... cell E40 should not read
"2". same goes for sheet 44, 39...

in sheets 31 to 35 there are dates under "functional location" which should not be the case


that's what i spotted so far, i think the other sheets should be fine =)

aaha - - you mean that your Month sheets are differing formats so the headers differ, the data is correct, check column M to column M


Note. mix'n match month weeks could be a problem

I will need to Date-seek column I for the latter sheets


---

[lalala]

ok thanks! im leaving work soon. i'll get back regarding this. =)

[lalala]

ok one last question for now. so the different headings resulting in the wrong information in the columns is because my original file has different amount of headings for the different months? i just realise this! how can i correct it without doing anything manually?

[Bryan Hessey]

ok one last question for now. so the different headings resulting in the wrong information in the columns is because my original file has different amount of headings for the different months? i just realise this! how can i correct it without doing anything manually?

Done, (well close enough for now, . . . fine-tune later)

Bryan
---
amended for 'I' column date from Sep. - still uses CTRL/Shift/O