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

# Vacation and Work - Connected Between

Hi

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.

David.

• ###### 1. Re: Vacation and Work - Connected Between

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.

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.