Hi Experts... I need your help.
I have two columns, where are dates and times of start and finish actions.
I need to create new column, where will be times between "start and finish" dates, but only in working hours and days. For example:
Working hours are 8:00 - 17:00, start is- 20.04.2017 16:00 and finish is- 21.04.2017 9:00. Clean working time of the action is 2 hours.
I also need to include only working days and except weekend in calculation.
Thanks for your advice.
Hi,
You need to calculate number of working days in a week first by creating series of formulas.
Here would be my approach:
find 1st working day of the week, last working day of the week and then working start day
= If <1st Working Day Number> = 7 Then 2 Else If <1st Working Day Number> = 6 Then 3 Else 1
Working End Day:
=If <Last Working Day> = 7 Then DayNumberOfMonth(LastDayOfMonth(<Date>)) - 1 Else If <Last Working Day > = 6 Then DayNumberOfMonth(LastDayOfMonth(<Date>)) - 2 Else DayNumberOfMonth(LastDayOfMonth(<Date>))
and find Weekends
1st Weekend of that week = If <1st Working Day Number> = 7 Then 1 Else If <1st Working Day Number> = 6 Then 2 Else 0
Note: I did not tried any of these formulas and found thru a source. Please try to execute each one with the logic on Month too.
<last day of month> =LastDayOfMonth(< Month>)
<Last day of last week> =DayNumberOfWeek(<last day of month >)
<first day of month> =DayNumberOfWeek(< Month>)
<days in a month> ==DaysBetween(< Month> ,<last day of month >)+1
<weekends> =(Truncate((DayNumberOfWeek(< Month>)+<Days in a month >)/7 ,0))
<Working Days> =Floor(<7 Working Days >-(<number of weekends>)*2)
<5 working Days> =If <first day of month > = 7 Or <Last day of last week > =6 Then (< Working Days >+1) Else < Working Days >
Below link for your reference.
https://csharan.wordpress.com/2012/04/21/22-working-days-calcualtion-in-web-intelligence/
Thanks,
Jothi