Skip to Content

Calculating working hours and days

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)
Add comment
10|10000 characters needed characters exceeded

  • 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.



  • Get RSS Feed

0 Answers