Mini Calendar: Incorrect Year and Date Population Errors

[Dalek_Rider]

Hello,

I am need of a bit of assistance. I will start but giving some background. I have recently started using Excel and have been successful learning through tutorials, reverse engineering, and trial and error. Recently my position at work has changed and I have to track various data, for things like KPI and Attendance. KPI's I have nailed down and have developed crude but fully functional sheets to take data from tables and fill out another sheet with charts that can be exported as pdfs. Lucky for me this information can be exported from a database at work so its easier to work with.

Unfortunately, I am struggling with creating an attendance tracker. The database used at work does not allow me to connect to it or export attendance information. I am currently using Excel 2013 (subscription version - latest update). I have looked through the templates and found 2 templates that each offer something I am looking for. The first being a mini calendar that shows each month and the dates. One row for the each month and then 42 columns for the dates. Very similar to the free template for the Chandoo calendars.

I am really struggling to understand how this works. I have gone into the templates for similar calendars and the name managers contain similar formulas, which I have copied and adjusted to match my cells/sheet names. but I keep getting errors.

Here are the defined names:
Calendar

=daysAndWks + DateOfFirst - WEEKDAY(DateOfFirst,2)

DateOfFirst

=DATE(Year('Annual Calendar'!$AN$5),MONTH('Annual Calendar'!$C9),1)

daysAndWks

=COLUMN(OFFSET(INDIRECT("$A$1"),0,0,1,42))-1

Looking at other Excel docs the cells where the Date number will be have {=Calendar} listed, but when I put that in the cell I get an ### error. I am not sure what I am doing wrong here.

Additionally when I change the Year, from anything other then 2015, it comes back with a date from the 1900s. I am currently double checking on that, since I seem to recall a solution somewhere.

My ultimate goal is to be able to change the year and have the dates correctly adjust to the day of the week. I plan to then use this as a template for the current members of my team and will then use a key like inputting an A into a date to show absent then being able to add those values. I think I have that sorted pretty well, but just need to get past this issue with generating the dates on the correct day and correctly updating the year.

Any help would be greatly appreciated. I have uploaded a file if anyone is able to take a gander. I can upload images of the two other calendars I am trying to build off of, but since they are excel templates from microsoft, I thought it best not to include those actual excel files.

Thanks for taking the time to look this over and again thanks for any help that anyone can offer.

 

[Hui]

@Dalek Rider

Firstly, Welcome to the Chandoo.org Forums

The Named Formula for daysAndWks should be:
=COLUMN(OFFSET(INDIRECT("$A$1"),0,0,1,42))-1

If you have a look at the file you will see there are extra " 's throughout the formula


see revised file attached

I added some CF to simplify the look

 

[Hui]

To use the Calendar formula
Select H9:AW9
Type =Calendar press Ctrl+Shift+Enter, not Enter

Copy H9:AW9
paste in the rows below

 

[Hui]

To change the year
Click in the cell next to select year
Type 1/1/2016, not 2016

 

[Dalek_Rider]

Thanks!

You good sir.. are a life saver. I had been trying to fiddle with this on and off for the past few days. I had been so close a few times then I would add or delete a few things trying to fix my mistakes and got sloppy with dbl "'s and copying bits from note pad which were not part of the formula.

The closest I had gotten was getting all the cells to give me the same date which was the correct date for the first Sunday of the 42 days. For example 28 across the board for January's row. I checked your file and made the changes but it still escaped me... until I saw the Ctrl+Shift+Enter.

Thanks a million, the conditional formats were my next tackle . Thank you again, I think that has me fully sorted. I'll likely be around as I have perused the site and see there's plenty more to learn.

Thanks,
Dalek_Rider