Skip to Content

Calculating working hours and days

Apr 24, 2017 at 11:37 AM


avatar image

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.

lzduy.png (7.6 kB)
10 |10000 characters needed characters left characters exceeded


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.



* Please Login or Register to Answer, Follow or Comment.

0 Answers