Forum Discussion

Chairod's avatar
Chairod
Copper Contributor
Jan 17, 2019

Excel Calendar adding extra days.

Anyone know how to eliminate the extra days being added to the end of each month on this Microsoft calendar?  
Excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 17, 2019

    It looks like that's Employee Attendance Record Template.

    They use formulas

    For the beginning of the month

    =IFERROR(IF(TEXT(DATE(Calendar_Year,ROW($A1),1),"ddd")=LEFT(C$5,3),DATE(Calendar_Year,ROW($A1),1),""),"")

    In the middle of calendar

    =IFERROR(IF(R6>=1,R6+1,""),"")

    and at the end of it

    =IFERROR(IF(AND(AM6>=1,AM6+1<=DATE(Calendar_Year,ROW($A1)+1,0)),AM6+1,""),"")

    You may safely copy cell from the end of the calendar with latest formula (e.g. in column AR) and paste it into the cells starting, let say, from column X or so.

SergeiBaklan's avatar
SergeiBaklan
MVP

Hi Charoid,

 

What do you mean under Not computing? I tested it with Employees #1 and #3, numbers in the boxes are the same.

 

To change the formula select cells AR6:AR17 and drag selection to the left till middle of the calendar. Please check attached changed file.

Chairod's avatar
Chairod
Copper Contributor
Jan 17, 2019
Thank you again Sergei. When I added an event on the employee leave tracker sheet, it would transfer to the calendar, but it did not add to the running total for each leave type listed on the bottom. Your copy works perfectly.

Resources