SOLVED

# Excel Calendar adding extra days.

Copper Contributor

# 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?

15 Replies

# Re: Excel Calendar adding extra days.

Hello,

can you post the formula that created the day number?

best response confirmed by Ingeborg Hawighorst (MVP)
Solution

# Re: 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.

# Re: Excel Calendar adding extra days.

I copied cells AL through AR and pasted the copied formulas in cells AE through AK and it removed all of the extra days in the month. Now each month only reflects day 1 through 28, 30 or 31 depending on its corresponding last day. Thank you for your help Sergei. I'm still a rookie with excel, but I'm learning everyday.

# Re: Excel Calendar adding extra days.

Ingeborg - thank you for responding. I received an answer that helped me, but I didn't want to leave you unrecognized. Thank you for being willing to assist me :)

# Re: Excel Calendar adding extra days.

Now that I've copied the formulas as mentioned the "Key Statistics" box counts are not computing.

The formula in the key stats boxes is as follows:

=SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],valSelEmployee,LeaveTracker[Start Date],">="&DATE(Calendar_Year,1,1),LeaveTracker[End Date],"<"&DATE(Calendar_Year+1,1,1))

=SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],valSelEmployee,LeaveTracker[Start Date],">="&DATE(Calendar_Year,1,1),LeaveTracker[End Date],"<"&DATE(Calendar_Year+1,1,1),LeaveTracker[Type of Leave],'Leave Types'!B4)

It was computing just fine until I copied and pasted the cells as previously discussed.

# Re: Excel Calendar adding extra days.

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.

# Re: Excel Calendar adding extra days.

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.

# Re: Excel Calendar adding extra days.

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.

# Re: Excel Calendar adding extra days.

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.

# Re: Excel Calendar adding extra days.

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

# Re: Excel Calendar adding extra days.

Hi Sir,

I would like to change the holiday from sat sun to Friday.

Could you plz help me for that

# Re: Excel Calendar adding extra days.

See my previous reply in this discussion. If you want Friday as the only weekend day, use NETWORKDAYS.INTL with 16 as 3rd argument.

For example, with dates in A2 and B2:

=NETWORKDAYS.INTL(A2, B2, 16)

# Re: Excel Calendar adding extra days.

Sergei, could you explain which part of the formula takes off the extra days?
I have the following in mine currently:
=IFERROR(IF(W6>=1,W6+1,""),"")
it's adding 4 days on to the end of the months currently.

# Re: Excel Calendar adding extra days.

When I try to use the =IFERROR(IF(AND(AG6>=1,AG6+1,<=DATE(Calendar_Year,ROW(\$A1)+1,0)),AG6+1,""),"")
it gives me the following error:
there's a problem with this formula.
not trying to type a formula?
when the first character is an equal (=) or minus (-) sign, excel thinks it's a formula.
etc. etc.

# Re: Excel Calendar adding extra days.

In

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

there is no cell reference before <=

It's not clear what you're trying to do...

1 best response

Accepted Solutions
best response confirmed by Ingeborg Hawighorst (MVP)
Solution

# Re: 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.