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

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.

Jerimax04's avatar
Jerimax04
Copper Contributor
May 25, 2021
Good day! I hope It will reach you, I would like to ask for your assistance how to make Friday as Weekday. After opening the Employee Attendance Record Template it is set to Saturday and Sunday as weekend. If you can kindly share information how to change the formula to Friday as Weekend.
Your reply will be much appreciated.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 25, 2021

    Jerimax04 

    Formatting on the Calendar View sheet:

    Select C6.

    On the home tab of the ribbon, select Conditional Formatting > Manage Rules...

    Select the last rule and click Edit.

    You'll see the formula

     

    =OR(LEFT(C$5,1)="S", COUNTIF(lstHolidays, C6)>0)

     

    Change it to

     

    =OR(LEFT(C$5,1)="F", COUNTIF(lstHolidays, C6)>0)

     

    2) NETWORKDAYS formulas. On the Calendar View and Employee Leave Tracker sheets, there are several formulas that use NETWORKDAYS. For example, in H20 on the Calendar View sheet:

     

    =NETWORKDAYS(DATE(Calendar_Year,1,1),EDATE(DATE(Calendar_Year,1,1),12)-1)

     

    Change this to

     

    =NETWORKDAYS.INTL(DATE(Calendar_Year,1,1),EDATE(DATE(Calendar_Year,1,1),12)-1,16,lstHolidays)

     

    Similar in H21.

    And in F4 on the Employee Leave Tracker sheet:

     

    =NETWORKDAYS([@[Start Date]],[@[End Date]],lstHolidays)

     

    Change this to

     

    =NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],16,lstHolidays)

     

    and fill down.

    • Jerimax04's avatar
      Jerimax04
      Copper Contributor
      May 25, 2021
      Dear Hans,

      I have done all your instructions and I really appreciate your time helping me in the formulas.
      I really had a hard time browsing for information how to edit the sheet as per our Company attendance. I'm really glad for your help.

      Thank you very much, but I hope this won't be the last time.

      Best regards,
      Jerica

Resources