SOLVED
Home

Excel Calendar adding extra days.

%3CLINGO-SUB%20id%3D%22lingo-sub-319859%22%20slang%3D%22en-US%22%3EExcel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68841i812CFA19586E9C3C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3EAnyone%20know%20how%20to%20eliminate%20the%20extra%20days%20being%20added%20to%20the%20end%20of%20each%20month%20on%20this%20Microsoft%20calendar%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-319859%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320473%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320473%22%20slang%3D%22en-US%22%3EThank%20you%20again%20Sergei.%20When%20I%20added%20an%20event%20on%20the%20employee%20leave%20tracker%20sheet%2C%20it%20would%20transfer%20to%20the%20calendar%2C%20but%20it%20did%20not%20add%20to%20the%20running%20total%20for%20each%20leave%20type%20listed%20on%20the%20bottom.%20Your%20copy%20works%20perfectly.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320467%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320467%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Charoid%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20do%20you%20mean%20under%20Not%20computing%3F%20I%20tested%20it%20with%20Employees%20%231%20and%20%233%2C%20numbers%20in%20the%20boxes%20are%20the%20same.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20change%20the%20formula%20select%20cells%20AR6%3AAR17%20and%20drag%20selection%20to%20the%20left%20till%20middle%20of%20the%20calendar.%20Please%20check%20attached%20changed%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320420%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320420%22%20slang%3D%22en-US%22%3E%3CP%3ENow%20that%20I've%20copied%20the%20formulas%20as%20mentioned%20the%20%22Key%20Statistics%22%20box%20counts%20are%20not%20computing.%3C%2FP%3E%3CP%3EThe%20formula%20in%20the%20key%20stats%20boxes%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(LeaveTracker%5BDays%5D%2CLeaveTracker%5BEmployee%20Name%5D%2CvalSelEmployee%2CLeaveTracker%5BStart%20Date%5D%2C%22%26gt%3B%3D%22%26amp%3BDATE(Calendar_Year%2C1%2C1)%2CLeaveTracker%5BEnd%20Date%5D%2C%22%26lt%3B%22%26amp%3BDATE(Calendar_Year%2B1%2C1%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(LeaveTracker%5BDays%5D%2CLeaveTracker%5BEmployee%20Name%5D%2CvalSelEmployee%2CLeaveTracker%5BStart%20Date%5D%2C%22%26gt%3B%3D%22%26amp%3BDATE(Calendar_Year%2C1%2C1)%2CLeaveTracker%5BEnd%20Date%5D%2C%22%26lt%3B%22%26amp%3BDATE(Calendar_Year%2B1%2C1%2C1)%2CLeaveTracker%5BType%20of%20Leave%5D%2C'Leave%20Types'!B4)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20computing%20just%20fine%20until%20I%20copied%20and%20pasted%20the%20cells%20as%20previously%20discussed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320326%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320326%22%20slang%3D%22en-US%22%3E%3CP%3EIngeborg%20-%20thank%20you%20for%20responding.%20I%20received%20an%20answer%20that%20helped%20me%2C%20but%20I%20didn't%20want%20to%20leave%20you%20unrecognized.%20Thank%20you%20for%20being%20willing%20to%20assist%20me%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320325%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68934i5BBD6E3831233D90%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3EI%20copied%20cells%20AL%20through%20AR%20and%20pasted%20the%20copied%20formulas%20in%20cells%20AE%20through%20AK%20and%20it%20removed%20all%20of%20the%20extra%20days%20in%20the%20month.%20Now%20each%20month%20only%20reflects%20day%201%20through%2028%2C%2030%20or%2031%20depending%20on%20its%20corresponding%20last%20day.%20Thank%20you%20for%20your%20help%20Sergei.%20I'm%20still%20a%20rookie%20with%20excel%2C%20but%20I'm%20learning%20everyday.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320001%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320001%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20looks%20like%20that's%20Employee%20Attendance%20Record%20Template.%3C%2FP%3E%0A%3CP%3EThey%20use%20formulas%3C%2FP%3E%0A%3CP%3EFor%20the%20beginning%20of%20the%20month%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(IF(TEXT(DATE(Calendar_Year%2CROW(%24A1)%2C1)%2C%22ddd%22)%3DLEFT(C%245%2C3)%2CDATE(Calendar_Year%2CROW(%24A1)%2C1)%2C%22%22)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EIn%20the%20middle%20of%20calendar%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(IF(R6%26gt%3B%3D1%2CR6%2B1%2C%22%22)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20at%20the%20end%20of%20it%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(IF(AND(AM6%26gt%3B%3D1%2CAM6%2B1%26lt%3B%3DDATE(Calendar_Year%2CROW(%24A1)%2B1%2C0))%2CAM6%2B1%2C%22%22)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EYou%20may%20safely%20copy%20cell%20from%20the%20end%20of%20the%20calendar%20with%20latest%20formula%20(e.g.%20in%20column%20AR)%20and%20paste%20it%20into%20the%20cells%20starting%2C%20let%20say%2C%20from%20column%20X%20or%20so.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319879%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Calendar%20adding%20extra%20days.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319879%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecan%20you%20post%20the%20formula%20that%20created%20the%20day%20number%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chairod
Occasional Contributor

1.PNGAnyone know how to eliminate the extra days being added to the end of each month on this Microsoft calendar?

 

7 Replies

Hello,

 

can you post the formula that created the day number?

Solution

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.

1.PNGI 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.

 

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 :)

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.

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.

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies