Calculated column not returning the correct number of days between two dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1105740%22%20slang%3D%22en-US%22%3ECalculated%20column%20not%20returning%20the%20correct%20number%20of%20days%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1105740%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20SharePoint%20Calendar%20that%20I'm%20using%20to%20track%20employee%20vacation%20time.%20One%20of%20the%20columns%20that%20I%20have%20is%20a%20calculated%20column%20which%20counts%20the%20number%20of%20workdays%20between%20the%20start%20%26amp%3B%20end%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I'm%20using%20is%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D(DATEDIF(%5BStart%20Date%5D%2C%5BEnd%20Time%5D%2C%22D%22))-INT(DATEDIF(%5BStart%20Date%5D%2C%5BEnd%20Time%5D%2C%22D%22)%2F7)*2-IF((WEEKDAY(%5BEnd%20Time%5D)-WEEKDAY(%5BStart%20Date%5D))%26lt%3B0%2C2%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20add%20a%20new%20event%20using%20the%20calendar%20form%20the%20formula%20works%20fine%20and%20correctly%20calculates%20the%20number%20of%20days%20off.%20However%2C%20when%20I%20add%20time%20to%20the%20calendar%20using%20my%20PowerApp%20the%20time%20is%20always%20short%20by%201%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20this%20might%20have%20to%20do%20with%20the%20site%20timezone%20or%20workweek%20settings%20but%20everything%20appears%20to%20be%20fine.%20The%20workweek%20is%20set%20to%20Monday%20-%20Friday%20and%20the%20region%20is%20EST.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHas%20anyone%20ever%20ran%20into%20a%20similar%20issue%3F%20Any%20thoughts%20on%20how%20this%20might%20be%20corrected%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1105740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Regular Visitor

I have a SharePoint Calendar that I'm using to track employee vacation time. One of the columns that I have is a calculated column which counts the number of workdays between the start & end date. 

 

The formula I'm using is:

=(DATEDIF([Start Date],[End Time],"D"))-INT(DATEDIF([Start Date],[End Time],"D")/7)*2-IF((WEEKDAY([End Time])-WEEKDAY([Start Date]))<0,2,0)

 

When I add a new event using the calendar form the formula works fine and correctly calculates the number of days off. However, when I add time to the calendar using my PowerApp the time is always short by 1 day.

 

I thought this might have to do with the site timezone or workweek settings but everything appears to be fine. The workweek is set to Monday - Friday and the region is EST.

 

Has anyone ever ran into a similar issue? Any thoughts on how this might be corrected?

0 Replies