Solved: SharePoint custom list calendar view End Date problem

%3CLINGO-SUB%20id%3D%22lingo-sub-355153%22%20slang%3D%22en-US%22%3ESolved%3A%20SharePoint%20custom%20list%20calendar%20view%20End%20Date%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355153%22%20slang%3D%22en-US%22%3E%3CP%3EUse%20Case%3A%3C%2FP%3E%3CP%3EYou%20need%20a%20calendar%20view%20for%20a%20custom%20list%20which%20has%20a%20Start%20Date%20and%20End%20Date%20.%20For%20example%3A%20events%2C%20training%2C%20travel%2C%20bookings%2C%20etc.%3CBR%20%2F%3E%3CBR%20%2F%3EProblem%3A%3C%2FP%3E%3CP%3EYou%20will%20face%20this%20peculiar%20problem.%20If%20you%20have%20set%20the%20column%20as%20Date%20Only%2C%20the%20End%20Date%20automatically%20takes%20its%20time%20as%2012%3A00%3A00%20am%20internally.%20As%20a%20result%2C%20the%20calendar%20visual%20shows%20event%20ending%20on%20previous%20date.%20So%2C%20if%20your%20event%20starts%2001-Feb-2019%20and%20ends%2005-Feb-2019%2C%20on%20the%20calendar%20it%20will%20render%20the%20event%20end%20as%2004-Feb-2019.%3CBR%20%2F%3E%3CBR%20%2F%3ESolution%3A%3C%2FP%3E%3CP%3ETo%20fix%20this%2C%20(assuming%20end%20date%20column%20name%20is%20called%20%3CSTRONG%3EEnd%20Date%3C%2FSTRONG%3E)%20create%20a%20calculated%26nbsp%3Bcolumn%20called%26nbsp%3B%3CSTRONG%3EActual_End%3C%2FSTRONG%3E(Format%3A%20Date%26amp%3BTime)%20with%20formula%20as%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%5BEnd%20Date%5D%2B%226%3A00%3A00PM%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E...%20the%20%22Time%22%20can%20be%20anything%20on%20the%20actual%20event%20end%20date%20other%20than%2012%3A00%20am.%20Modify%20the%20calendar%20view%20and%20set%20this%20calculated%20column%20as%20the%20%3CSTRONG%3EEnd%3A%3C%2FSTRONG%3Ein%20Time%20Interval%20section.%20Obviously%2C%20you%20can%20name%20your%20columns%20whatever%20you%20want.%3CBR%20%2F%3E%3CBR%20%2F%3EAfter%20struggling%20for%20long%2C%20I%20finally%20arrived%20at%20this%20solution%2C%20hope%20this%20helps.%26nbsp%3B%20Do%20reply%20if%20there%20are%20other%20means%20to%20address%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-355153%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514803%22%20slang%3D%22en-US%22%3ERe%3A%20Solved%3A%20SharePoint%20custom%20list%20calendar%20view%20End%20Date%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514803%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F163964%22%20target%3D%22_blank%22%3E%40Yatin%20Purohit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20also%20having%20the%20same%20issue%20on%20our%20calendars.%20Would%20it%20not%20be%20simpler%20to%20change%20the%20column%20setting%20from%20%22date%20only%22%20to%20%22date%20and%20time%22%3F%20And%20then%2C%20when%20the%20user%20creates%20an%20event%2C%20they%20can%20add%20a%20time%20of%20%3CSPAN%3E6%3A00%3A00PM%3C%2FSPAN%3E%20in%20that%20field%20going%20forward.%20Or%20would%20changing%20the%20column%20settings%20disrupt%20the%20data%20in%20legacy%20events%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514865%22%20slang%3D%22en-US%22%3ERe%3A%20Solved%3A%20SharePoint%20custom%20list%20calendar%20view%20End%20Date%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514865%22%20slang%3D%22en-US%22%3EYes%2C%20you%20are%20right.%20Creating%20a%20date%20and%20time%20column%20type%20will%20solve%20this%20too.%20However%2C%20in%20scenario%20where%20user%20is%20required%20to%20only%20enter%20date%20and%20not%20time%2C%20user%20interface%20will%20be%20confusing%20or%20irritating.%3CBR%20%2F%3EIn%20my%20scenario%2C%20users%20block%20training%20rooms%2C%20very%20often%20across%20days.%20The%20requirement%20(also%20users%20habit)%20is%20to%20only%20enter%20booking%20dates%20and%20not%20time.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514900%22%20slang%3D%22en-US%22%3ERe%3A%20Solved%3A%20SharePoint%20custom%20list%20calendar%20view%20End%20Date%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F163964%22%20target%3D%22_blank%22%3E%40Yatin%20Purohit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20what%20if%20I%20change%20the%20settings%20on%20the%20current%20column%3F%20In%20other%20words%2C%20I%20don't%20create%20a%20new%20column%20I%20just%20change%20the%20settings%20on%20the%20existing%20column.%20My%20fear%20is%2C%20doing%20this%2C%20it%20will%20mess%20up%20past%20data.%20We%20can%20enforce%20entering%20the%20time%2C%20so%20that's%20not%20a%20problem.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514918%22%20slang%3D%22en-US%22%3ERe%3A%20Solved%3A%20SharePoint%20custom%20list%20calendar%20view%20End%20Date%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514918%22%20slang%3D%22en-US%22%3EAbsolutely%20changing%20current%20column%20type%20will%20work.%20Bear%20in%20mind%20that%20previous%20entries%20may%20have%20time%20as%2000%3A00%3A00%20and%20that%20may%20have%20impact%20on%20calendar%20display.%20.%20Possibly%20use%20a%20Power%20Automate%20flow%20to%20populated%20the%20time.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515033%22%20slang%3D%22en-US%22%3ERe%3A%20Solved%3A%20SharePoint%20custom%20list%20calendar%20view%20End%20Date%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515033%22%20slang%3D%22en-US%22%3E%3CP%3E%40Yatin%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcellent%2C%20thanks%20so%20much%20for%20the%20advice!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Use Case:

You need a calendar view for a custom list which has a Start Date and End Date . For example: events, training, travel, bookings, etc.

Problem:

You will face this peculiar problem. If you have set the column as Date Only, the End Date automatically takes its time as 12:00:00 am internally. As a result, the calendar visual shows event ending on previous date. So, if your event starts 01-Feb-2019 and ends 05-Feb-2019, on the calendar it will render the event end as 04-Feb-2019.

Solution:

To fix this, (assuming end date column name is called End Date) create a calculated column called Actual_End (Format: Date&Time) with formula as:

=[End Date]+"6:00:00PM"

... the "Time" can be anything on the actual event end date other than 12:00 am. Modify the calendar view and set this calculated column as the End: in Time Interval section. Obviously, you can name your columns whatever you want.

After struggling for long, I finally arrived at this solution, hope this helps.  Do reply if there are other means to address this issue.

 

5 Replies

Hi @Yatin Purohit 

 

We are also having the same issue on our calendars. Would it not be simpler to change the column setting from "date only" to "date and time"? And then, when the user creates an event, they can add a time of 6:00:00PM in that field going forward. Or would changing the column settings disrupt the data in legacy events?

Yes, you are right. Creating a date and time column type will solve this too. However, in scenario where user is required to only enter date and not time, user interface will be confusing or irritating.
In my scenario, users block training rooms, very often across days. The requirement (also users habit) is to only enter booking dates and not time.

@Yatin Purohit 

 

But what if I change the settings on the current column? In other words, I don't create a new column I just change the settings on the existing column. My fear is, doing this, it will mess up past data. We can enforce entering the time, so that's not a problem. 

Absolutely changing current column type will work. Bear in mind that previous entries may have time as 00:00:00 and that may have impact on calendar display. . Possibly use a Power Automate flow to populated the time.

@Yatin

 

Excellent, thanks so much for the advice!