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
)
)
)
)
)
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โฆ
Thanks @Steve! Iโll give this a go and let you know what I come up with.
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.
@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?