Great formulas

You can use the following formulas in SharePoint site columns. You can place any of the following formulas into a calculated site column.

AcknowledgeDate1 = End date

Created = Start date

You can specify an exact start and end time for each day. The total number of hours is slightly low or high for modifications times that fall outside the start and end time defined in the formula or if the modification time falls on a weekend.

=((INT([AcknowledgeDate1])-INT(Created)-INT((WEEKDAY([Created])+INT([AcknowledgeDate1])-INT([Created]))/7)-INT((WEEKDAY([Created]-1)+INT([AcknowledgeDate1])-INT([Created]))/7))*(“18:00”-“06:00″)+MOD([AcknowledgeDate1],1)-MOD([Created],1))*24

The next formula gives you the total number of hours regardless of the time the shift ends and starts. The formula assumes a 12 hour shift. If you want to change that, substitute 8 for 12 for an 8 hour shift.

((INT([AcknowledgeDate1]-[Created])*12+INT(MOD([AcknowledgeDate1]-[Created],1)*12)+INT(MOD(MOD([AcknowledgeDate1]-[Created],1)*12,1)/60))-((INT(DATEDIF([Created],[AcknowledgeDate1],”D”)/7)*2-IF((WEEKDAY([AcknowledgeDate1])-WEEKDAY([Created]))<0,2,0))*12)

The following formula rounds to days and doesn’t display hours. The formula excludes weekends and holidays.

=IF(AND((WEEKDAY([Due Date],2))<(WEEKDAY(Created,2)),((WEEKDAY(Created,2))-(WEEKDAY([Due Date],2)))>1),(((DATEDIF(Created,[Due Date],”D”)+1))-(FLOOR((DATEDIF(Created,[Due Date],”D”)+1)/7,1)*2)-2),(((DATEDIF(Created,[Due Date],”D”)+1))-(FLOOR((DATEDIF(Created,[Due Date],”D”)+1)/7,1)*2)))

Number of hours between two dates

B2 = end date

A2 = start date

=(((FLOOR(B2-A2,0.5))-INT((FLOOR(B2-A2,0.5)/7))*2-IF((WEEKDAY(B2)-WEEKDAY(A2))<0,2,0))*(“16:45”-“07:45”)+MOD(B2,1)-MOD(A2,1))*24

Leave a Reply