Hi,
I am trying to calculate the Actual reported time. Actual Reported Time = Clock in Time – Current Time
It is throwing the below error while running the test
Can someone help please?
Thanks
Subha
Try using the calculated field that shows the Current Time, instead of the Constants.CurrentTime
Calvin, I am not finding any CurrentTime option in calculated field.
What is creating this field?
I created the current time calculated field as below
Okay. I see the issue now…
the formula should have the LaborDtl.ClockinTime in the DateAdd()
something like
(convert(varchar, DateAdd(ms,Constants.CurrentTime * 1000, LablorDtl.ClockInTime), 108)
FWIW - I haven’t checked that, as I don’t know what the data type of the ClockInTime is.
Calvin,
The problem is I am not able to do arithmetic function - subtraction in varchar datatype. how to convert the current time varchar data type to integer in the time format.
Currently we have Labordtl.ClockInTime in integer data type. I have to convert the current time to integer data type to do the subtraction function.
I meant in the ActualReportedTime calculated field.
And look at it as two steps:
- Calculate the new time - Using DateAdd(), you can add (or subtract) the ClockInTime from the CurrentTime. with:
DateAdd(ms, -Constants.CurrentTime*1000, LaborDtl.ClockinTime) - convert that result to a string - using convert():
`convert(varchar, [the stuff from step 1] , 108)
And FTR I would have this column be of type Time, or DateTime. That way you could use it for math against other values, or even filter on it.
edit
I just now see that the CurrentTime is in seconds since midnight. So first you have to make a date time from Constants.Today & Constants.CurrentTime.
DateAdd(s,Constants.CurrentTime,Constants.Today) - That will give you the current time(and date) in a DateTime value
Next, subtract the ClockinTime (which is in hours) from the above. Do this by using the hour interval, and a negative sign in front of the ClockInTime
DateAdd(hour, -LaborDtl.ClockinTime,DateAdd(s,Constants.CurrentTime,Constants.Today))
If you really want it as a string add the `convert(varchar, .the above…, 108)
Thanks Calvin
Hi! Bringing back this very old post I stumbled on and am trying to accomplish the same thing; calculate the number of hours an operator is clocked in by taking labordtl clockinTime and constants.currentTime.
The issue and seems same issue as above is I can not find a way to subtract these values to get the desired outcome.
Both calc fields set as datetime.
Are you trying to calculate the LaborHrs realtime, or just want the ClockIn and ClockOut as datetime?
Yes thats correct real time. For dashboarding purposes I do not want to wait for an end activity transaction to trigger labor hours. At any point in time I want to display the labor hours accrued on each active job.
Easiest way to get ClockInDate and ClockOutDate with it being accurate if they cross 24 hour mark.
CAST(DATEADD(minute, LaborDtl.ClockInMInute, '1953-10-30') AS datetime) as [Calculated_ClockInDate]
CAST(DATEADD(minute, LaborDtl.ClockOutMinute, '1953-10-30') AS datetime) as [Calculated_ClockOutDate],
Realtime LaborHrs (your calculated field would be decimal) you could tweak it if you want a smaller formula using the 1953 method
CASE WHEN LaborDtl.ActiveTrans = 0 THEN
LaborDtl.LaborHrs
ELSE
CAST(
(DATEDIFF(minute, DATEADD(second, LaborDtl.ClockInTime * 3600, CAST(LaborDtl.ClockInDate as DateTime)), GETDATE()) / 60.00) AS decimal(5,2))
END
I always love when I see snippets of your time handling functions, cool stuff @hkeric.wci
Very cool thanks for taking the time! Forgive me as calculated field code is not my expertise. So sorry if I am completely missing something.
Is this supposed to be a single calc field or each line represents its own calc field(this is how I entered it) I assume as DateTime?
This is definitely moving in the right direction but the value are not adding up correctly or as I would expect.
Single active transaction clock in at 11:51.
Ah the LaborDtl writes a bogus value in until they actually clock out… let me check something I may have the same bug.
To get the ClockOutDate you might have to add
CAST(IIF(LaborDtl.ActiveTrans = 1, NULL, DATEADD(minute, LaborDtl.ClockOutMinute, '1953-10-30')) AS datetime) AS [Calculated_ClockOutDate]
Because the LaborHrs are correct, you have an ActiveTrans.
I did the same thing a couple of years ago. Made an SSRS gantt with it. Can confirm, ClockInMinute and ClockOutMinute are a least bad way to go, and definitely take note of the Epicor Epoch (1953-10-30) for future reference.
The only thing I did different was sub in current time for ClockOutMinute where a labor transaction is active and pass the Active value with the data to highlight those records. All good either way.
For the record shift breaks might mess up your numbers. If workers are clocked in over lunch and breaks defined in Shift Entry, labor and burden may be reduced by those times. There are ways to subtract those times if it really has to be done. Not pretty ways, or beginner friendly ways, but there are ways.
With some…alot of help from AI and multiple iterations I was able to get a formula to work.
CASE
WHEN LaborDtl.ActiveTrans = 0 THEN
CAST(LaborDtl.LaborHrs AS decimal(9,4))
WHEN LaborDtl.ClockInMinute IS NULL THEN
NULL
ELSE
CAST(
(
DATEDIFF(
minute,
‘10/30/1953’,
DATEADD(second, Constants.CurrentTime, Constants.Today)
)
- LaborDtl.ClockInMinute
) / 60.0
AS decimal(9,4)
)
END
This is giving me the act labor hours spent on starting from clock in time.