Excluding weekends (using DATEDIFF function to identify employees first 3 days as training)
A search for Exclude Weekends and Exclude Weekends using DateDiff Function returned a few results, none of which would seem to work for what we're trying to do so checking to see if we can get some help on this!
We're trying to add, in the dataflow itself, as part of the output dataset, a transform that would add a new column by calculating:
1) an employees first 3 SUCCESSIVE dates of work that we would generally consider to be TRAINING, i.e. Employee Start Date - Date when difference is <= 2
2) exclude weekends since there is no training on weekends, meaning an employee who started mid-week, say on a Thursday, would have trained on Thursday, Friday, and the following Monday
3) the goal is to identify those days as YES in a new column (EMPLOYEE TRAINING), and use that column as a quick filter to exclude training days for billing reconciliation with external partners who provide staffing solutions for our call center.
This is what we have in the dataflow to determine JUST the first 3 days of employment (seems to work for some, not for others, kind of hit or miss, but obviously not at all for those who have training before and after a weekend):
CASE WHEN DATEDIFF(`Date`,`Start Date`) <=2 AND `User Company` = 'XXX' THEN 'Yes' ELSE 'No' END as `Employee Training`
** The Date field is derived from a DateTime field of the date their shift started, and Start Date is pulled from an Employee Roster (Domo Webform) that shows an employees hire date (column is to the far right, not visible).
Any ideas on how to exclude weekends, preferably using a transform within the dataflow, though we're open to a beast mode at the card level if necessary? Thanks in adance for your time & assistance!