none
Converting Excel formula to SharePoint Calculated column

    질문

  • I am trying to calculate the difference between two date and time, considering work hours (7:30 to 4:30, Monday to Thursday & 7:30 to 4:00 Friday) work days (Monday to Friday) and Holidays. I have and excel formula that works fine, but when i try it as a calculated column it returns 0. please i need help.

    See Excel formula below;

    =IF(OR(End Time<Start Time,Modified<Created),0,
    (NETWORKDAYS(Created,Modified,HolidayList)
    -(NETWORKDAYS(Created,Created,HolidayList)
    *IF(MOD(Created,1)>End Time,1,
    (MAX(Start Time,MOD(Created,1))-Start Time)
    /(End Time-Start Time)))
    -NETWORKDAYS(Modified,Modified,HolidayList)
    *IF(MOD(Modified,1)<Start Time,1,
    (End Time-MIN(End Time,MOD(Modified,1)))
    /(End Time-Start Time))))
    *(End Time-Start Time)*24

    Start Time = Start of Work Hours

    End Time =  End of work Hours

    Created = Requests Creation Time

    Modified = Request Closure time

    HolidayList =  Predefined Holiday list

    See SharePoint formula below;

    IF(OR([End Time]<[Start Time],[Modified]<[Created]),0,
    (WEEKDAY([Created],[Modified])
    -(WEEKDAY([Created],[Created])
    *IF(MOD([Created],1)>[End Time],1,
    (MAX([Start Time],MOD([Created],1))-[Start Time])
    /([End Time]-[Start Time])))
    -WEEKDAY([Modified],[Modified])
    *IF(MOD([Modified],1)<[Start Time],1,
    ([End Time]-MIN([End Time],(MOD([Modified],1)))
    /([End Time]-[Start Time]))))
    *([End Time]-[Start Time])*24

    Thanks.

    2018년 5월 18일 금요일 오전 8:36

모든 응답

  • Hi,

    If you want to calculate the working days between two dates, you can consider to use the formula below in SharePoint:

    F(ISERROR(DATEDIF([StartDate],[EndDate],”d”)),””,(DATEDIF([StartDate],[EndDate],”d”))+1-INT(DATEDIF([StartDate],[EndDate],”d”)/7)*2-IF((WEEKDAY([EndDate])-WEEKDAY([StartDate]))<0,2,0)-IF(OR(AND(WEEKDAY([EndDate])=7,WEEKDAY([StartDate])=7),AND(WEEKDAY([EndDate])=1,WEEKDAY([StartDate])=1)),1,0)-IF(AND(WEEKDAY([StartDate])=1,(WEEKDAY([EndDate])-WEEKDAY([StartDate]))>0),1,0)-IF(AND(NOT(WEEKDAY([StartDate])=7),WEEKDAY([EndDate])=7),1,0))

    More information:

    Calculate number of working days between two dates

    Thanks

    Best Regards


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    2018년 5월 21일 월요일 오전 9:03
  • Thanks, however the working hours is not working.
    2018년 5월 21일 월요일 오전 9:16