Share via

any year calendar template does not show dates

Anonymous
2012-02-09T03:48:56+00:00

I downloaded the Calendar (any year) and the Calendar creator (any year) from the 2012 Calendars templates (both by Microsoft Corporation). Both show all the dates in January; the first two weeks of February but in rows 4 and 5 of the month; then all cells in other months display blank.

The formula in the cells are:

Forumula in cell row 1 column 1 of January (which showed all dates in the 31 cells correctly for year 2012)

=IF(AND(YEAR(JanSun1)=$A$1,MONTH(JanSun1)=1),JanSun1, "")

displayed as 1

formula in cell 2 of January which for year 2012

=IF(AND(YEAR(JanSun1+1)=$A$1,MONTH(JanSun1+1)=1),JanSun1+1, "")

displayed as 2

formula in cell row 1 column 1 of February which displayed as blank

=IF(AND(YEAR(FebSun1)=$A$1,MONTH(FebSun1)=2),FebSun1, "")

formula in cell row 3 column 7 of March which displayed as blank

=IF(AND(YEAR(MarSun1+20)=$A$1,MONTH(MarSun1+20)=3),MarSun1+20, "")

all cells for March through December for year 2012 displayed a blank.

How can I get these templates to display the dates correctly for all months?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-04-09T09:48:11+00:00

    This problem is caused by fomula's used in the original template.

    For example, AprSun1 function was defined as

    =DATEVALUE("4/1/"&TheYear)-WEEKDAY(DATEVALUE("4/1/"&TheYear))+1

    This works perfectly on a PC with "Regional Settings" as United States, because  DATEVALUE function expects  mm/dd/yyyy   format date string.

    It breaks on Regions where date format is  dd/mm/yyyy

    A robust formula for AprSun1  should be

    =DATEVALUE("1-Apr-"&TheYear)-WEEKDAY(DATEVALUE("1-Apr-"&TheYear))+1

    I hope somebody from MS will be able to fix this in the template file.

    Regards,

    • Aung

    7 people found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-02-27T15:16:15+00:00

    Since the formula works on some cells, copy and paste the formula from a working cell into the blank cell. Ensure to add the column count and check if it helps.

    For example in cell H24 the formula would be

    =IF(AND(YEAR(MarSun1+19)=$A$1,MONTH(MarSun1+19)=3),MarSun1+19, "")

    if you want to use it for cell I24 use this

    =IF(AND(YEAR(MarSun1+20)=$A$1,MONTH(MarSun1+20)=3),MarSun1+20, "")

    Also check the format of the cell i.e. right click on the cell click on Format Cell in Number tab under Category it should be Custom and under Type it should be 'd'.

    0 comments
  2. Anonymous
    2012-02-18T02:16:36+00:00

    I think I didn't respond correctly to Anula's reply: I did a reply to his message with a report on what I did following his suggestions. That was on 10Feb, the day Anula replied. I haven't had any further response to help me resolve the failure of these templates to work correctly on my PC.

    I have a screen shot of what they do if anyone can tell me how I attach/include it in a reply.

    Hoping this can be resolved soon.

    Mike

    0 comments
  3. Anonymous
    2012-02-10T00:24:17+00:00

    Thanks, Anula. Opened Excel in safe mode and got the same result (I have a jpeg screen shot but can't see an option to attach it).

    Although it didn't work in safe mode, I thought I'd try disabling the COM add-ins but ran into another problem: when I tried to untick one I got the message that only an administrator could connect or disconnet it. I have Windows7 Home Premium SP1 and am logged in as an administrator user (as per Control Panel | User Accoounts)

    I have four COM add-ins:

       Acrobat PDFMaker Office COM Addin

       AveryAddin.Connect

       Google Desktop Office Addin

       Send to Bluetooth

    Incidently, when I change the year the dates/day-of-week for January and those showing in incorrect cells for February adjust correctly.

    As these templates work at your end there must be something wrong with my configuration. Any suggestions on how I can proceed to fix this will be appreciated.

    0 comments
  4. Anonymous
    2012-02-09T15:57:13+00:00

    It works fine in our end.

    Open Excel in safe mode and then open the template. Follow the steps given below to open Excel in safe mode:

    Click on Start< All Programs< Accessories< Run< Type ‘excel /safe’ (without quotes) and click on Ok

    Note: There should be a space between 'excel' and '/'

    If the template works fine in safe mode, disable the add-ins. Follow the steps given below on how to disable Add-ins in Excel 2007:

    Step1: Click the Office Icon and Click Excel Options.

    Step2: In the left hand side of the Excel Options dialog box, click Add-Ins.

    Step3: Under ‘Manage’ Click the COM Add-ins

    Step4: Uncheck all the Add-Ins listed under COM Add-ins.

    Step5: Click close and restart Excel

    0 comments