Error in calculated field for Date/Time for All-day events

%3CLINGO-SUB%20id%3D%22lingo-sub-121440%22%20slang%3D%22en-US%22%3EError%20in%20calculated%20field%20for%20Date%2FTime%20for%20All-day%20events%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-121440%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20calendar%20web%20part%20that%20shows%20upcoming%20events%20from%20a%20corporate%20calendar.%26nbsp%3B%20We%20have%20a%20calculated%20column%20that%20just%20shows%20the%20date%20of%20the%20event%20start.%26nbsp%3B%20The%20formula%20for%20the%20calculated%20column%20is%20%3CFONT%20color%3D%22%230000ff%22%3E%3CSTRONG%3E%3D%5BStart%20Time%5D%3C%2FSTRONG%3E%3C%2FFONT%3E%20.%26nbsp%3B%20This%20works%20for%20our%20events%2C%20EXCEPT%20if%20the%20event%20is%20an%20%22all-day%22%20event.%26nbsp%3B%20In%20that%20case%2C%20the%20start%20date%20is%20listed%20as%204%20hours%20off%2C%20so%20it%20shows%20as%20occurring%20the%20previous%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20means%20that%20for%20an%20event%20which%20is%20set%20to%20start%2011%2F1%2F2017%2012%3A00AM%20and%20end%20at%2011%2F1%2F2017%2011%3A59PM%20EDT%2C%20the%20value%20of%20the%20%3D%5BStart%20Time%5D%20formula%20is%2010%2F31%2F2017%208%3A00%20PM.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOur%20calendar%20and%20all%20systems%20looking%20at%20this%20data%20are%20set%20to%20the%20same%20time%20zone.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelieve%20it%20or%20not%20we%20found%20someone%20with%20this%20same%20issue%20in%202008!%20Here's%20the%20link%3A%20%3CA%20href%3D%22https%3A%2F%2Fsocial.msdn.microsoft.com%2FForums%2Fsharepoint%2Fen-US%2F8dc97589-cdd7-4b2e-bb5e-45511d1b1a1f%2Fdiscrepancy-in-calculated-field-using-datetime%3Fforum%3Dsharepointcustomizationlegacy%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsocial.msdn.microsoft.com%2FForums%2Fsharepoint%2Fen-US%2F8dc97589-cdd7-4b2e-bb5e-45511d1b1a1f%2Fdiscrepancy-in-calculated-field-using-datetime%3Fforum%3Dsharepointcustomizationlegacy%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyway%20to%20fix%20this%20other%20that%20the%20long%20and%20complicated%20formula%20in%20that%20post%3F%26nbsp%3B%20It's%20almost%20a%20decade%20later.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-121440%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-300901%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20in%20calculated%20field%20for%20Date%2FTime%20for%20All-day%20events%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300901%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20update%20on%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

We have a calendar web part that shows upcoming events from a corporate calendar.  We have a calculated column that just shows the date of the event start.  The formula for the calculated column is =[Start Time] .  This works for our events, EXCEPT if the event is an "all-day" event.  In that case, the start date is listed as 4 hours off, so it shows as occurring the previous day.

 

This means that for an event which is set to start 11/1/2017 12:00AM and end at 11/1/2017 11:59PM EDT, the value of the =[Start Time] formula is 10/31/2017 8:00 PM. 

 

Our calendar and all systems looking at this data are set to the same time zone. 

 

Believe it or not we found someone with this same issue in 2008! Here's the link: https://social.msdn.microsoft.com/Forums/sharepoint/en-US/8dc97589-cdd7-4b2e-bb5e-45511d1b1a1f/discr...

 

Is there anyway to fix this other that the long and complicated formula in that post?  It's almost a decade later.

1 Reply

Any update on this?