Hello I am trying to modify a baq calculated field to for a shift that carries over past midnight.
I need any labor details between 12am and 5am to show on the previous createdate. so minus 1 day from the create date if labor was transacted during this time. If this makes sense.
shift runs from 4:30-4:30
Field used LaborDtl_CreateDate and LaborDtl_CreateTime
Thank you!
Not probably the recommended way but you might be able to get what you want with the formula below.
Convert time to human readable format.
Time (data type = nvarchar (x20)) = Ice.StringTime(LaborDtl.CreateTime, âHH:MM:SSâ)
Use the Time above to calculate date:
New_Date (data type = date) =
(case when Time < â05:00:00â
then dateadd(day, -1, LaborDtl.CreateDate)
else LaborDtl.CreateDate
end)
I am open to suggestions if you know of a better way to handle this I am trying to create a dashboard for a supervisor to easily break Mon-Thurs earned hours down and sum. Currently this shift will have hours on say 4/3 @4:00AM on their last clock out and at 4:30 PM on 4/3 when they return he would like an way to view thisâŚ
Lisa,
I am not that familiar with creating dashboards. If i am understanding you correctly, then you want all the hrs from each employee who worked from 12:00 AM to 5:00 AM to be considered as previous day, correct? So, say if an employee logs in at 4:30 pm on 04/03/2019 and clocks out at 4:30 AM on 04/04/2019, you want the whole time calculated for 04/03/2019, instead of splitting it between 2 days, correct?
How are the employees logging the time? Are they logging time for the jobs in real time or someone entering the time later on? If they are logging the hrs in real time then you could use the formula above and then group by the âNew_Dateâ, âEmployeeâ and the aggregate sum the labor hrs).
In this way, you will see the New_date, Employee, total hrs for that date
This often doesnât work for peopleâs specific business needs, but Epicorâs way of handling this is the field PayrollDate. This date is intended to represent the date the labor entries count towards even if they were performed on a different date due to a shift spanning midnight. I believe itâs only at the LaborHed level, and it calculates based on which date the majority of their time took place. It can easily get jumbled if people donât clock in and out when they come and go, or if they stay clocked in for days at a time.
Take a look and see if this might work for you.
You are exactly correct they would be considered the previous day. They are logging in real time. Thank you so much I am going to try this out now.
Would I take your formula and paste this directly into the calculated field expression box?
yes. You could paste it into your formula editor.
Ice.StringTime(LaborDtl.CreateTime, âHH:MM:SSâ)
when you paste it, for some reason you might have to remove the quotes around âHH:MM:SSâ and then put the quotes back again.
I would agree with Craig that we normally use Labor.Dtl_Payroll date, but in your instance since youâre using real time recording the create date and time might work for you. I normally enter all the time manually the following day, so the create date and time would not work for me to query since it was not create in real time.
If it doesnât workout for you then you could try Craigâs method and link laborHead to labor dtl file and get try to use the LaborHead.payroll date to see if that gives you what you want.
If the above formula doesnt work, try to do it using payroll date from the labor head table. it might be what you need based on the description of the field.
that worked thank you so much this is exactly what we needed 
