1

I wanted to calculate hours between two dates excluding weekends in SharePoint calculated column.

I can get the hours using following formula but not sure how to exclude weekends?

=TEXT([enddate]-[startdate],"h")

I got this formula which gives me date difference in days, hrs and minute excluding weekend -

=DATEDIF(StartDate,EndDate,"D")-IF(WEEKDAY(EndDate)=7,FLOOR((DATEDIF(StartDate,EndDate,"D")+WEEKDAY(StartDate))/7,1)*2,FLOOR((DATEDIF(StartDate,EndDate,"D")+WEEKDAY(StartDate))/7,1)*2+1)+IF(WEEKDAY(StartDate)=7,2,1)&TEXT(MOD(EndDate-StartDate,1),"hh""""mm""""")

Applied the same but having following issue - StartDate - 8/4/2016 12:22 PM EndDate - 8/5/2016 9:22 AM

Calculated col with above formula gives me "12059" ie. 1 day, 20hrs and 59 min

Actually I have to put the logic if calculated col > 24 hrs , then have to do some actions... But with above formula, if date changes, tehn it will give me 1 day always...

  • you might subtract 48 h. Will that solve your requirement? – Patrick Aug 10 '16 at 7:41
  • There are at least 3 blogs out there with the answer, I don't have them at hand. Use Google – Danny '365CSI' Engelman Aug 10 '16 at 7:45
  • sure I will google it now, – Meenakshi Dhar Aug 10 '16 at 8:17
  • Hi Patrick- Substract 48 hrs? Not getting this one... – Meenakshi Dhar Aug 10 '16 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.