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
Vacation%20and%20Work.twbx 19.9 KB