# Extended Leave - IF max hours worked that fall between 2 dates exceeds Leave Hours Taken

#### Kelli Webb

##### Member
Hello,

I am trying to establish if someone has been absent for the full calendar month i.e. 1/4/2021 - 30/4/2021 based on numerous leave records.

Column J - I figured out the formula for those who were on leave prior & post April. My issue is for those employees who have broken leave in the month.

Column I - Is the max days they work per month that falls between 1/4/2021 - 30/4/2021 factoring in Part-time employees.

Column K - The presumption is if their total leave exceeds the figure in column I, they would be deemed as Yes for extended leave.

If you have any questions please don't hesitate to contact me.

Take care, Kelli

#### Attachments

• 28.4 KB Views: 15

#### vletm

##### Excel Ninja
Kelli Webb
Have You open somewhere in file ... what/where is their total leave value?
Should I find that term somewhere?
I did one guess which 'almost' give same result as Your 'should be' ... + two more "Yes"
... and You could modify J-column formula as You need.

#### Attachments

• 23.4 KB Views: 3

#### Kelli Webb

##### Member
Kelli Webb
Have You open somewhere in file ... what/where is their total leave value?
Should I find that term somewhere?
I did one guess which 'almost' give same result as Your 'should be' ... + two more "Yes"
... and You could modify J-column formula as You need.
Hi Vletm,

In column I is the total Days per month the employee is expected to work - previously this was called hours incorrectly.

The criteria is
1. If there leave starts on or before 1/4/2021 and ends on or after 30/4/2021 = Yes; or
2. If there leave total days taken that falls in April is or greater than the Days per Month = Yes (so we can capture part-time employees, who quite often put in broken leave applications).
The +two more "Yes" that you picked up did start on the 1st or finished on the 30th, but they weren't away for the entire month.

I would love to be able to adjust your formula, but this is beyond my capability.

If you have any advice, i'm open for it.

thanks & take care, Kelli

#### Attachments

• 29.9 KB Views: 4

#### vletm

##### Excel Ninja
Kelli Webb
As I have asked:
Have You open somewhere in file ... what/where is their total leave value?
Should I find that term somewhere?

Your If you have any advice, i'm open for it.
Answer to my question,
because without their total leave value, I cannot continue.

Of course, I could offer variations column-by-column or that value is out of that sheet (random)...

#### Kelli Webb

##### Member
Kelli Webb
As I have asked:
Have You open somewhere in file ... what/where is their total leave value?
Should I find that term somewhere?

Your If you have any advice, i'm open for it.
Answer to my question,
because without their total leave value, I cannot continue.

Of course, I could offer variations column-by-column or that value is out of that sheet (random)...
Hi Vletm,

The term / period is in cell B1:C1 1/4/2021 - 30/4/2021.

The total leave taken for each employee is in column D & could be on several rows as broken leave.

I could do a pivot table to show the total leave taken, but I won't be able to identify what was taken in April.

It is really the broken leave for the part-timers that is bringing my formula undone & that I would like to find a solution to if you can help.

I really do appreciate you taking the time to help me & I'm not even sure if there is a solution e.g. if a part-time person is away the 26/4/2021 - 6/5/2021 the best we could do is determine the work days x their fte - but without their roster we will may never get a true result.

this is making my head hurt kind regards, Kelli

Last edited:

#### vletm

##### Excel Ninja
Kelli Webb
their total leave value

I guess that I find above term from Your writing.
J-column has text if yes, if something ...
K-column has that periods used leaves
L-column has that months (which has given with cells B1 & C1) days of leaves

Still J20 would be yes ... because 12 is more than 11.0 (cell I20) between B1 & C1 ... hmm?
If should compare more or equal then add `=` to cell J3 formula after `>`.

#### Attachments

• 24.1 KB Views: 1

#### Kelli Webb

##### Member
Kelli Webb
their total leave value

I guess that I find above term from Your writing.
J-column has text if yes, if something ...
K-column has that periods used leaves
L-column has that months (which has given with cells B1 & C1) days of leaves

Still J20 would be yes ... because 12 is more than 11.0 (cell I20) between B1 & C1 ... hmm?
If should compare more or equal then add `=` to cell J3 formula after `>`.
Hi Vletm,

I do like you pushing me out of my comfort zone.

With row 20, I see you have calculated the number of days from the start of leave to 30/4/2021 - but this includes the weekend/s
Q. are you able to exclude the Public Holidays & Weekends I've added in column O?

In cell N20 I've used NETWORKDAYS < the 1 Public holiday that falls in their leave, then x their fte = 7.5 BUT this is counting up to 10/5/2021 past my range. The correct answer for row 20 is 4.5 days & is less than column I, and would be No.

I feel we are getting so close & I really love some of what you've done & some of what I've done, but I don't know how to pull it all together.

It is getting late here, may I ask where you're from?

Kelli

#### Attachments

• 32.1 KB Views: 4

#### vletm

##### Excel Ninja
Kelli Webb
... hmm?
Your files has shows Start- and EndDates (headers B2 & B3).
I know and I've noticed that there could be millions different rules eg with leaves. ( ps I don't have leaves. )
Based Your writings - there were (or how?) any hints - how You would like to calculate those?
I modified my formula (J-column). I used different function than You.
I noticed that You have use ... some FTE there too ... I added that unknown feature with my formula ( You can modify that part if it is always used!)
I marked with yellow background color that row 20 as well as how those dates would effect to K-column results (range B27:E28)

It's not late here ... actually I soon late to do my normal tasks.
You can ask ... You're from 'down under' ... and ... our feet are almost opposite.

#### Attachments

• 26.8 KB Views: 1

#### bosco_yip

##### Excel Ninja
is expected to work - previously this was called hours incorrectly.
Try, this formula solution based on your post #1 file.

In J3 CSE formula (Ctrl+shift+Enter) copied down :

=IF(AND(MIN(IF(\$A\$3:\$A\$25=A3,\$B\$3:\$B\$25,""))<=A\$1,MAX(IF(\$A\$3:\$A\$25=A3,\$C\$3:\$C\$25,""))>=B\$1),"Yes","")

#### Attachments

• 24 KB Views: 4