Calculating Working Hours – hours in last day

…this post was published on 2016 March 31.

The final step is to calculate working hours in the last day. The procedure is similar with working hours in first day​

We need first, 2 new columns, calculated columns, which will calculate for EndDate the working hours.  The names for these 2 calculated columns, type DateTime including Time, are:
FirstWorkingHour_LastDay
LastWorkingHour_LastDay

The formula to calculate them are:
FirstWorkingHour_LastDay​​=DATE(YEAR(EndDate);MONTH(EndDate);DAY(EndDate))+(60*8+30)/24/60​​

LastWorkingHour_La​stDay==DATE(YEAR(EndDate);MONTH(EndDate);DAY(EndDate))+(60*16+30)/24/60

I the first one, (60*8+30)/24/60 represent the calculation for 8.30 am. If in your case the working days starts at 10.00am, yYou will use this:(60*10+0)/24/60
In the second one (60*16+30)/24/60 represent the calulation for the end working time: 16.30. You can change the formula and for 17.30, you can use this formula (60*17+30)/24/60

Attention. If your work interval is diff than 8 hours, you should change that value in the formula in case ii!

Because we don’t use validation, limitation for StartDate and EndDate columns, we should have 3 situations
i ) EndDate’s hour is less than 8.30 in the morning
ii ) EndDate’s hour is between 8.30 am and 16.30
iii ) EndDate’s  is more than 16.30 ​

​​More sintetic, the values will be:

​case i) ​case ii) ​case iii)
0 minutes
1440*​(EndDtae – FirstWorkingHour_LastDay​)
540 minutes ( 16.30-8.30)

We create a new calculated column, with the name: WorkingHours_LastDay​ as numeric with 2 decimals

The formula for this is:
WorkingHours_LastDay​ =(IF(1440*(LastWorkingHour_LastDay-EndDate)>0;IF((FirstWorkingHour_LastDay-EndDate)>0;0;1440*(EndDate-FirstWorkingHour_LastDay));540))/60

BUT , we don’t have working hours Saturday and Sunday, this mean that we need to eliminate these values for such of days

So the complete formula for WorkingHours_LastDay​ is
WorkingHours_​Last​Day​ =(IF(OR(WEEKDAY(EndDate)=1;WEEKDAY(EndDate)=7);0;IF(1440*(LastWorkingHour_LastDay-EndDate)>0;IF((FirstWorkingHour_LastDay-EndDate)>0;0;1440*(EndDate-FirstWorkingHour_LastDay));540)))/60

The results are:

Previous Step Main topic Next Step
This entry was posted in CalculatedColumn, Sharepoint Online, Uncategorized. Bookmark the permalink.