Compute work time

Steve
Participant V

Given a start date & time (DateTime column) and a number of work hours (Hours column, type Decimal), calculates the finish date & time to the nearest hour. Assumes the work day starts at 8:00 AM ("08:00:00") and ends at 5:00 PM ("17:00:00"). Uses WORKDAY() to compute dates.

IF(
  (
    ([DateTime] + ("000:00:00" + CEILING([Hours])))
    <= (DATETIME(DATE([DateTime])) + ("17:00:00" - "00:00:00"))
  ),
  ([DateTime] + ("000:00:00" + ROUND([Hours]))),
  (
    DATETIME(
      WORKDAY(
        DATE([DateTime]),
        CEILING(
          ([Hours] - TOTALHOURS("17:00:00" - TIME([DateTime])))
          / 8.0
        )
      )
    )
    +
    (
      ("08:00:00" - "00:00:00")
      +
      ROUND(
        [Hours]
        - TOTALHOURS("17:00:00" - TIME([DateTime]))
        -
        (
          (
            CEILING(
              ([Hours] - TOTALHOURS("17:00:00" - TIME([DateTime])))
              / 8.0
            )
            - 1
          )
          * 8.0
        )
      )
    )
  )
)
17 7 1,845
7 REPLIES 7

Oceans
Participant I

This works great @Steve!

Can we throw another wrench into the operation? How would this look if we were to add the following variablesโ€ฆ

#1 - Holidays

#2 - A [START DATETIME] column that represents the DateTime the job actually started which would then look past the [EST START DATETIME] column, add the [EST LOAD HRS] and give us a realtime [EST FINISH DATETIME]

Factoring in these two variables would completely wrap up the scheduling automation:)

Thanks!

Oceans

This would be reasonably simple, as the WORKDAY() function has support for them. You just have to put together a way to supply the list of holidays to WORKDAY(). For example:

WORKDAY(
  DATE([DateTime]),
  CEILING(
    ([Hours] - TOTALHOURS("17:00:00" - TIME([DateTime])))
    / 8.0
  ),
  SELECT(
    Non-Work Dates[Date],
    ([Date] >= DATE([_THISROW].[DateTime]))
  )
)

Ugh. Iโ€™m going to leave this as an exercise for the readerโ€ฆ

Oceans
Participant I

Thanks @Steve! Iโ€™ll give this a go and let you know what I come up with.

AlexM
Participant V

Hi @Steve ,

Somehow on this topic, I am struggling with one.

I need to count the days worked in full per month.
I need to count the days worked partially per month and add the hours for it.

The applicability of this is:

Worker (truck driver) gets paid by the hour.
He starts a run on the 20โ€™Th at 3AM of the month and finishes on the
5โ€™Th at 10PM.

Desired outcome:

JANUARY WAGE:
Full days: 11 days (21-31)
Hours on partially worked days: 3 (20โ€™Th of Jan)

FEBRUARY WAGE:
Full days: 4 days (1-4)
Hours on partially worked days: 22 (5โ€™Th of Feb)

My real problem is identifying the days he worked considering that he only inputs the departure DateTime and the arrival DateTime.

Not making a record for every days is a must, unfortunately. That would have been way easier.

At the moment I have done all based by the run

(HOUR([STOP]-[START])+(MINUTE([STOP]-[START)/60))*[RATE]

Thanks a lot!

UNTESTED!!!

[start] and [finish] are assumed to be DateTime values.

Number of calendar days in the time span as a Number value:

(HOUR(DATE([finish]) - DATE([start]) + 1) * 24)

Total length of time span as a Duration value:

([finish] - [start])

Time the first day as a Duration value:

(MAX(LIST([finish], DATETIME(DATE([start]) + 1))) - [start])

Time the final day as a Duration value:

([finish] - MIN(LIST([start], DATETIME(DATE([finish])))))

Thank you!

I know this bit. To make it shorter:

Start: 29.01.2021 / 10:00
Finish: 05.02.2021 / 18:00

How many days in January?
How many days in February?

Managed to sort the hours out by adding 3 columns to the Runs table:

[Begining] - stores the hours from the run that started last month and ended this month -> finish - start of the month

IF(AND(MONTH([Last Run].[Start])<MONTH([Start]),
MONTH([Last Run].[Stop])=MONTH([Start])),HOUR([Last Run].[Stop]-(EOMONTH([Start], -1)+1)+(MINUTE([Last Run].[Stop]-(EOMONTH([Start], -1)+1))/60),"")

[Middle] - stores the hours for this run if it started and ended in the same month

IF(MONTH([Stop])=MONTH([Start]),HOUR([Stop]-[Start])+(MINUTE([Stop]-[Start]))/60,"")

[End] - stores the hours for runs that started this month end ended next month -> end of this month - start

IF(MONTH([Stop])>MONTH([Start]),HOUR(EOMONTH([Start], 0)+1-[Start])+(MINUTE(EOMONTH([Start], 0)+1-[Start]))/60,"")

Then the wages table will add all these 3 columns for a specific month where [Month]=MONTH[Start].

Just need to get the actual number of full days and partial days.

EugeneB
Participant V

@Steve based on this formula & logic, if i the [DateTime] is 01/06/2021 04:00:00 PM with [Hours] 5 hours, suppose the answer should return as 02/06/2021 12:00:00PM right?

However when i setup, test case with the logic, it is just + 5 hours to the [DateTime] something not right?

Top Labels in this Space