Great formulas for SharePoint calculated columns
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
You must be logged in to post a comment.