1 Reply Latest reply on Nov 12, 2018 12:05 PM by Dan Cory

    Vacation and Work - Connected Between

    David Gabra



      The following analysis should be based on an employee to employee basis - so fixed on that field.  - this is column A [employee code] (column A)


      I need to show is a new calculated field column

      1)--> Which employee does not have any vacation or sickness charged against him (column B - Reporting Code)? I have 10000s - so need this done automatically.


      Refer to column E for the expected results.


      2) For employee that do have vacation or sickness charged - I need to sum the hours. (column C) so that I ONLY see the sum of the vacation hours in a new column.


      Refer to column G for the expected results.


      3) This I believe is the real tricky part and real important for me.

      I need to states how many months or (even days if easier) are remaining from the 'Start Date' in column D to the end of the year based on the start date.

      so any date in 2016 until 31/12/2016

      so any date in 2017 until 31/12/2017

      so any date in 2018 until 31/12/2018


      Refer to column G for the expected results.


      Thank you


      I hope my example is clear enough.



        • 1. Re: Vacation and Work - Connected Between
          Dan Cory

          See attached workbook.


          I created a set to define "Non Work Codes". You can also do this directly in a calculation if you want.


          The calculations were:

          IF MAX([Non Work Codes]) THEN "Yes Vacation or Sickness Recorded" ELSE "Only Work Recorded - No Vacation Recorded" END

          If any value is a Non Work Code, then there was vacation.


          SUM(IF [Non Work Codes] THEN [Hour] END)

          Add up the hours if it is a non work code.


          DATEDIFF('month',[Start Date],DATEADD('day',-1,DATETRUNC('year',DATEADD('year',1,[Start Date]))))

          This takes the start date, adds a year, goes to the start of that year, then goes to the previous day. So now you have the last day of the current year.

          The outermost DATEDIFF lets you count the months between the two dates. Change it to 'day' if you want to count days.

          Your example data had the wrong value for 333.

          Your example data also had no value for 444. If you meant only to compute this number if there was some vacation, then change it to:

          IF MAX([Non Work Codes]) THEN MAX(DATEDIFF('month',[Start Date],DATEADD('day',-1,DATETRUNC('year',DATEADD('year',1,[Start Date]))))) END



          1 of 1 people found this helpful