- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Convert data from monthly format into weekly
I have my data in a monthly format. But I want to convert it into a weekly format.
My column data type is text currently. And I'm unable to convert it into date data type.
Expected Output :
Sr.No. Customer Code Jan23(Week1) Jan23(Week2) Jan23(Week3) Jan23(Week4) Jan23(Week5)
Every month should divide into weeks.
I don't have a date column.
Can anyone help?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

1) in power query divided sales into daily sales and created a date in the same place
2) I created a calendar, if you don't like this calendar you can create your own
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

1) in power query divided sales into daily sales and created a date in the same place
2) I created a calendar, if you don't like this calendar you can create your own
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Great. You did fantastic work. But I have got an error.
"Column 'Value' in the table cannot be found or may not be used in this expression. Column 'Value' in the table cannot be found or may not be used in this expression. Column 'Value' in the table cannot be found or may not be used in this expression."
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

in this version I made a small change, removed the weekend days
https://1drv.ms/u/s!AiUZ0Ws7G26RhxQzx1qDV_e2UulW?e=Dj5M3M
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

In the Second Calender. also some totals are wrong.
It differs by 1.
I'm attaching an image for your reference.
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

only need to round to two decimal places
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

https://www.dropbox.com/scl/fi/g2vcupkp6ghzykfubcj1p/Merged-Forecast.xlsx?dl=0&rlkey=p35g27v9nxmcsqq...
Please check this data.
There are 3 sheets.
In first, Data is in weekly basis.
In second and third, Data is in monthly basis.
Now I want to append all these sheets and final result should displayed in weekly basis for all of these.
Please help.
And also tell me if this conversion and Append possible in Excel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Done.Thank you so much for your help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

look again carefully what and how I did, I don’t know what you have
the "Value" column is the sum column
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Failed to save modifications to the server. Error returned: 'Cannot convert value 'May'23' of type Text to type Date.
The current operation was cancelled because another operation in the transaction failed.
'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

give the file to see what is causing this error
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sr.No | Code | Jan23 | Feb23 | March23 | April23 | May23 | Total |
1 | 575 | 200 | 100 | 50 | 60 | 80 | 490 |
2 | 125 | 40 | 30 | 46 | 70 | 90 | 286 |
I have data in this monthly format.I want to convert it into weekly format without a date column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,
Remove the Total column. Select the first 2 columns, right click and click on "Unpivot Other Columns". Rename the Attribute column to Dae and ensure that this column has the Data Type of Date. Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Week number. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. To your visual, drag the Month name and Week number column from the Calendar Table.
Hope this helps.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank yo so much. It works. But now how to divide that value of a month into weeks equally?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,
I can try to help if you share the download link of the PBI file. In that PBI file, ensure that there is a Calendar Date with a week number column.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,
Also, share the PBI file with a calendar table and week number column.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I don't have Date column in data.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

In the Table that you have shared, the column headings are Month and year, When you Unpivot, the Attribute column's data type can be conveted into Date.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
02-10-2023 01:37 AM | |||
03-02-2024 08:57 AM | |||
04-05-2024 10:22 AM | |||
Anonymous
| 07-07-2020 12:23 PM | ||
08-15-2023 02:44 PM |
User | Count |
---|---|
141 | |
115 | |
83 | |
63 | |
48 |