Compute work time

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
        )
      )
    )
  )
)
8 Likes

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… :thinking:

4 Likes

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

1 Like