Converting data into a monthly calendar format/visual

Hi all,

I have a long list of numerical values with an associated month / date / day. I would like to convert these figures to view them in a "calendar" visual for easier reading. Is there an easier way to do it to avoid manually entering the data?

I am attaching the spreadsheet where I enter a few fields manually to get a better idea of what i am on about.

Any help would be appreciated.

Thank you!

Will you only have one number for each day, or several?

Do you want to see a full year in your calendar, or just one month, where you can select the month from a drop-down?

Do you want to select the year from a drop-down?

Pete

Hi Pete,

Thank you for your reply.

I will only have one number for each date.

Yes a full year, no need for drop downs. Once the 2020 data is complete i may go back to previous years and just have each year on separate sheets.

Let me know if you need further info

Thanks.

A few more questions before I get stuck into this:

1. I'm not sure why you show the data in a pivot table - is it getting the data from somewhere else?

2. On the calendar, do you want to show the day number for each date?

On any calendar grid showing a full week going across, there will be occasions when you will need 6 weeks to show the full month, i.e. in a 31-day month which starts on a Saturday, or a 30-day or 31-day month which starts on a Sunday, although on other occasions the month will fit within 5 weeks, in which case the line for the 6th week will be empty. This happens about 2 or 3 times each year.

One way of avoiding this is to show the "orphan" days (i.e. those that spill into the 6th week) on the top line of the calendar for that month, as obviously the Monday and Tuesday will otherwise be empty.

3. Which of these approaches would you like me to take?

4. Do you want to show the calendar as one month by 12, or say as 3 months wide by 4? This latter display will fit more easily on a page if you want to print it out.

5. Do you want to have the calendar in a separate sheet, or on the same sheet as the raw data? If on a separate sheet, then you could have just one calendar and select the year, with your data arranged on one sheet for each year.

Hope this helps.

Pete

Hi Pete,

Thank you for getting back, ill try to reply being as clear as possible.

1. I originally converted the raw data into a pivot table to calculate the totals for each day from Jan - Oct 2020

2. No i don't need the actual date. Days of the week within in month will work for me.
If there is a 6th week with no data, that is fine. If you would like me to manually complete Jan so you get a better idea of what im after, i can do that.

3. If i understood correctly - i will go with the 'If there is a 6th week with no data, that is fine'

4. I would like 1 month by 12. The main reason being that i will be adding further information and calculations (ex: average for all Mondays, avg per week, etc)

5. I don't need the raw data on the same sheet. The only issue ill have if its set up to select the year is the additional calculations i will be working with later. So if it's just one sheet with all the months under each other it will be easier for me.

Also, i really don't expect you to do this for me, if you would help me with a month or 2 or explain how it's done, i will then complete it myself - i don't want to come across as lazy!

Appreciate your time with this!

Thanks.

Okay, I've partially set this up for you in the attached file, so you can study it and complete it yourself.

Instead of using a pivot table, I've extracted your data and just listed it in two columns for the date and number in a sheet that I've called raw_data.

In order to extract the data by date, you will need to have a calendar of dates. This could be done on alternate rows, showing the dates on one row and the values on the next, and so on through the year - the rows with the dates in could be hidden to emphasise your values.

Instead, though, I've chosen to display a calendar on the right hand side of the calendar sheet, with the values shown in a corresponding grid on the left side.

For the calendar, I've put the year of interest in cell Y1 - you can put a different year in that cell, and the calendar will automatically adjust (the cell could be a data validation drop-down). The calendar itself is thus stand-alone in those columns.

Although the month headings display the month and year, they are formatted to display only that - they really contain a date, which is the first of the month. So, cell X3 contains the formula:

=DATE($Y$1,1,1)

which will return the date of 1st January for the chosen year. The formula for February is:

=EOMONTH(X3,0)+1

which will give the 1st of February, and similar for the other month headings. For each month, we need to determine on which day the 1st of that month occurs, and this is what the formulae in X5 to AD5 do, as the 1st will always occur on that top row of the month. For subsequent days the date is just the previous date plus one. As months occupy a differing number of weeks (minimum 4), the formulae beyond that have to check that the previous date plus one is actually in the same month, and no month will need more than 2 days on the last (6th) week.

For February, the same formulae can be used, referring to the date in the month header rather than in X3, so the block of formulae from X5:AD10 can just be copied down. For March, given that the month header is the same as for February, the whole block from X11:AD18 can be copied down into cell X19, and similar for the other months (I've left it for you to complete that).

To display the totals for each day, you just need this formula in B5:

=IF(X5="","",IFERROR(VLOOKUP(X5,raw_data!$A:$B,2,0),"")

which can be copied across and down to suit the month of January. The block for January can just be copied down to suit the next month, and this can be continued for the other months.

Hope this helps.

Pete