Vacation and Work Days

[david gabra]

Hey
I am not sure if you need macro or normal excel can help.
I need to know please the following;
This must be done per employee [employee code] (column A)

I need to show is a new 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.

 

[chirayu]

I got confused by point 3. As you fiscal year ends in December, shouldn't employee 333 be 9 months rather than 8 months? attached sample

 

[david gabra]

My mistake - only human
how would i change it - if the end of FY was always September 30th.

you are amazing!!!!!!!!!!!!!!!!!!!!!!

 

[Khalid NGO]

Hi David,

See if these works:

1)
=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

2)
=IF(B2="Work","",SUM(SUMIFS(C:C,A:A,A2,B:B,{"vacation","Sickness"})))

3)
=EDATE(D2,12-MONTH(D2))-D2

Regards,

 

[david gabra]

Hi

Can you change this

=IF(COUNTIFS($A:$A,$A11)=COUNTIFS($A:$A,$A11,$B:$B,"Work"),"Only Work Recorded - No Vacation Recorded","Yes Vacation or Sickness Recorded")

I wanted to rather state - not equal to "work" or "sickness"

rather than if equal to work

Please help - i tried and error all the time

 

[david gabra]

Hi
How do you state or sickness

=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

is this correct?

=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation" or "sickness"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

Hi David,

See if these works:

1)
=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

2)
=IF(B2="Work","",SUM(SUMIFS(C:C,A:A,A2,B:B,{"vacation","Sickness"})))

3)
=EDATE(D2,12-MONTH(D2))-D2

Regards,

 

[chirayu]

What is your fiscal year? OCT-SEP?

 

[david gabra]

Yes

 

[chirayu]

attached

 

[david gabra]

Hi
How do you state or sickness

=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

is this correct?

=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation" or "sickness"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

or

how do I state:

 

[david gabra]

and =IF(AND(COUNTIFS($A:$A,$A7)<>COUNTIFS($A:$A,$A7,$B:$B,"Work"),$B7="vacation" or $b7="sickness"),SUMIFS($C:$C,$A:$A,$A7,$B:$B,"<>Work"),"")

I added "or sickness" and did not work

 

[david gabra]

I feel really stupid - sorry

 

[Khalid NGO]

Hi David,

See the bold Red part updated:

=IF(A2="","",IF(SUM(COUNTIFS(A:A,A2,B:B,{"vacation","sickness"})),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

Regards,

 

[chirayu]

btw managed to get working days bit in my file as well. attached

 

[david gabra]

Hi
Can you explain i words, if you don't mind how the below work please.
I do not understand them.
thanks
David.

Hi David,

See if these works:

1)
=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

2)
=IF(B2="Work","",SUM(SUMIFS(C:C,A:A,A2,B:B,{"vacation","Sickness"})))

3)
=EDATE(D2,12-MONTH(D2))-D2

Regards,

 

[Khalid NGO]

Hi David,

Let me try:
1)
=IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded"))

First IF is checking if A2 is blank then leave the result to blank

2nd IF is checking the count of word "vacation" for particular ID, if the count is greater than zero, it will show the text "Yes Vacation or Sickness Recorded", else "Only Work Recorded - No Vacation Recorded"

2)
=IF(B2="Work","",SUM(SUMIFS(C:C,A:A,A2,B:B,{"vacation","Sickness"})))

First IF is checking if B2 is equals to "work" then leave the result to blank, else go for the SUM.

SUMFIS formula by nature works with AND condition, here we used {array} and warped with SUM for OR condition, that means if the cell either contains vacation OR sickness, it will sum for both the criteria.

3)
is simple EDATE and i realized after posting, i should have used EOMONTH instead of EDATE.

If you break each function and use Evaluate Option (or F9) you will easily understand the formula.

You also have the solutions provided by Chirayu.

HTH,
Take Care