# 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))`

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
• Hi,

What is the data in Holiday list ? Please provide some sample data for us so that we could find a solution to meet your requirement.

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월 26일 토요일 오전 9:13