Forum Discussion
Excel Calendar adding extra days.
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.
Your reply will be much appreciated.
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.
- 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