Calculating Working Hours – hours in first day

…this post was published on 2016 March 31.

The last 2 steps are those for calculating hours in first  and last day.

Let’s begin with the first day.
As I said in main post, we assume that the working day begins at 8.30am and stops at 16.30 in the afternoon. We don’t take care for lunch break. 🙁
This mean that for a full day we have 8 working hours.​
We need first, 2 new columns, calculated columns, which will calculate for StartDate  day working hours.

The names for these 2 calculated columns, as DateTime including Time, are
FirstWorkingHour_FirstDay
LastWorkingHour_FirstDay

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

LastWorkingHour_FirstDay=DATE(YEAR(StartDate);MONTH(StartDate);DAY(StartDate))+(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.
You 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 values in the formula in case ii!

Because we don’t use validation, limitation for StartDate and EndDate columns, we should have 3 situations

i ) StartDate’s hour is less than 8.30 in the morning
ii ) StartDate’s hour is between 8.30 am and 16.30
iii ) StartDate’s  is more than 16.30

More sintetic, the values will be:

​case i) ​case ii) ​case iii)
540 minutes ( 16.30-8.30)​
1440*​(LastWorkingHour_FirstDay​-StartDate)
0 minutes​

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

The formula for this will be:
WorkingHours_FirstDay​ =(IF(1440*(LastWorkingHour_FirstDay-StartDate)>0;IF((FirstWorkingHour_FirstDay-StartDate)>0;540;1440*(LastWorkingHour_FirstDay-StartDate));0))/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_FirstDay​ is
WorkingHours_FirstDay​ =(IF(OR(WEEKDAY(StartDate)=1;WEEKDAY(StartDate)=7);0;IF(1440*(LastWorkingHour_FirstDay-StartDate)>0;IF((FirstWorkingHour_FirstDay-StartDate)>0;540;1440*(LastWorkingHour_FirstDay-StartDate));0)))/60

The results are:

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