none
Finding difference between two dates (containing timestamp) - exclude weekends in the calculation RRS feed

  • Question

  • Hi !

    How would this be possible to do ?
    I have 2 columns "Start Date" and "End Date" that contains time stamp (for example: 5/6/2019 4:46:00 PM). I would like to take the difference of the two dates to calculate number of days (including hours which will most likely be contained as part of decimals). I also want to ensure weekends are excluded.

    Thanks all for your help!

    -Sam

    Thursday, August 15, 2019 5:36 AM

Answers

  • You can add a column with this code:

    [
        ListOfAllDays = List.Dates(Date.From([DateStart]), Number.From(Date.From([DateEnd]) - Date.From([DateStart])) + 1, #duration(1,0,0,0)),
         NumberOfWeekdays = List.Count(List.Select(ListOfAllDays, (x) => Date.DayOfWeek(x, Day.Monday) < 5 )), 
         StartAdjustment = if Date.DayOfWeek([DateStart], Day.Monday) < 5 then Number.From(Time.From([DateStart])) else 0,
         EndAdjustment = if Date.DayOfWeek([DateEnd], Day.Monday) < 5 then 1 - Number.From(Time.From([DateEnd])) else 0,
         Result = NumberOfWeekdays - StartAdjustment - EndAdjustment
        ][Result]

    It assumes that the column with your StartDate is called "StartDate" and "EndDate" respectively. Otherwise you have to adjust these expressions in the code.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by SamNaik Sunday, August 18, 2019 6:05 AM
    • Marked as answer by SamNaik Saturday, August 24, 2019 6:23 AM
    Friday, August 16, 2019 5:29 PM
    Moderator