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

Wow!

Thank you so much for this. Just by going over it briefly, i can assume this was more work than i thought it would be. It is exactly what i was looking to do. I'll go through it carefully later (when i get some peace and quiet) in detail to understand and hopefully learn something to complete it myself and most likely work on the previous years too

Genuinely appreciate this, saved me a tonne of time.

Also, apologies for my delay in replying, just got a few minutes to take a look.

Many thanks again!!
Schaak

It's basically copy and paste each month block to the same area below, in order to complete it.

If you wanted to be able to look at other years, controlled by the year in Y1 on the Calendar sheet, then assuming you have data sheets in the same format and which are named with some reference to the year (e.g. data_2018, data_2019, data_2020, and so on) instead of just raw_data, then you could change the formula in B5 to this:

=IF(X5="","",IFERROR(VLOOKUP(X5,INDIRECT("'data_"&$Y$1&"'!A:B"),2,0),""))

Copy across and down as required, within each monthly block.

If, after studying it in more detail, you think that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thanks for the rep.

Just as a little exercise, I've put together a different-looking calendar for the same data.

This one does not need a separate calendar, as the dates are implicit in the position of each cell. Of course, the days of the week are all mixed up, so I have used conditional formatting to shade the weekend days. The calendar works with this formula in C4:

=IFERROR(VLOOKUP(DATE($B$1,ROWS($1:1),C$3),INDIRECT("'data_"&$B$1&"'!A:B"),2,0),"")

which is copied across and down as required. The formula for 29th February is slightly different, to account for non-leap years.

Just another way of looking at your data.

Hope this helps.

Pete

Hi Pete,

I started working on the spreadsheet you sent and it's going very well. As i said previously i am adding other data to it now, so things are coming together

Thank you for the 2nd spreadsheet, i see if i can adapt it other reports i do to make my life easier.

With regards to the reputation (star) i had done that on a previous post, now if i click on another post to add reputation it returns this: You must spread some Reputation around before giving it to Pete_UK again. Whenever i give others rep ill try come back and give you more