Absence Periods Calculation

[dele]

Can you help, I have attached an absence sheet which shows each employee number and each period of absence - start and end date.

I need a formulae which will show how many periods of absence each employee has had bearing in mind all employees are on the same sheet. For example, staff no 88 should show as 2 periods of absence as the first four absences are linked, the formulae should then move on to the next staff no and count again

Hope this makes sense

Any help is appreciated

[Sam Capricci]

what does the start and end date signify?

[dele]

what does the start and end date signify?

Hi, it is the start and end of each absence period. Its just the way our system records information

Thanks

[humdingaling]

does this work for you?

[dele]

does this work for you?

that seems to have worked thanks very much, would i be able to dump in data in the same format into the first 3 columns (staff, start and end) and the formulae will pick up the rest?

[dele]

Hi, is there anyway of changing the code to ignore weekends so that the absence is linked.

Appreciate the help

Marv

[humdingaling]

answer to first question is yes
to the 2nd question
i have an idea to use either Weekday() formula in conjunction with deducting end date with start date of following line but i am unsure of syntax that will align with what you want
Can you clarify how you want to skip the weekend?

[dele]

Hi, sorry bit late for a reply.. Did you manage to be able to adjust the formula on my previous sheet to ignore weekends. I just need it to ignore the Saturday and Sundays that pop up so it treats any date on a friday or monday as linked. Regards

[dele]

answer to first question is yes
to the 2nd question
i have an idea to use either Weekday() formula in conjunction with deducting end date with start date of following line but i am unsure of syntax that will align with what you want
Can you clarify how you want to skip the weekend?

Hi, sorry bit late for a reply.. Did you manage to be able to adjust the formula on my previous sheet to ignore weekends. I just need it to ignore the Saturday and Sundays that pop up so it treats any date on a friday or monday as linked. Regards

[humdingaling]

dele...dont usually proceed with anything if people stop responding
it usually means they figured it out and plain moved on

also as i really don't know exactly what you want i didnt know how to proceed

i added some lines and a table to your original sheet
please indicate the lines you want skipped?

6 and 7 = Saturday and Sunday respectively

[dele]

dele...dont usually proceed with anything if people stop responding
it usually means they figured it out and plain moved on

also as i really don't know exactly what you want i didnt know how to proceed

i added some lines and a table to your original sheet
please indicate the lines you want skipped?

6 and 7 = Saturday and Sunday respectively

Hi, understood, got completely caught in something else. Yes lines 6 and 7 need to be ignored with both 1 and 5 linked to form one period. For example, staff member 439 should have 3 periods not 5 (2 really because there are 2 bank holidays but ignore, i dont want to over complicate) Thanks again

[humdingaling]

hmm missing something
439 i still get 4

439 17-Feb-14 21-Feb-14 439
439 4-Mar-14 24-Mar-14
439 25-Mar-14 28-Mar-14 439
439 31-Mar-14 31-Mar-14
439 1-Apr-14 4-Apr-14 439
439 7-Apr-14 11-Apr-14
439 12-Apr-14 17-Apr-14
439 21-Apr-14 25-Apr-14 439

[dele]

hmm missing something
439 i still get 4

439 17-Feb-14 21-Feb-14 439
439 4-Mar-14 24-Mar-14
439 25-Mar-14 28-Mar-14 439
439 31-Mar-14 31-Mar-14
439 1-Apr-14 4-Apr-14 439
439 7-Apr-14 11-Apr-14
439 12-Apr-14 17-Apr-14
439 21-Apr-14 25-Apr-14 439

hi, thought i replied to this thread.
28 Mar and 31 Mar are linked as they are both 1 and 5
4 Apr and 7 Apr are linked as they are both 5 and 1
So, dates are 17 Feb-21Feb, 4 Mar-17 Apr and 21 Apr-25 Apr = 3 periods

[humdingaling]

based on that criteria
i made some amendments to the formula
439 shows as 3 now

used column F again just so the formula looks neater to troubleshoot
if the final formula you can incorprate the weekend() bit into the if formula

[dele]

based on that criteria
i made some amendments to the formula
439 shows as 3 now

used column F again just so the formula looks neater to troubleshoot
if the final formula you can incorprate the weekend() bit into the if formula

Great, thanks again for the help
D

[dele]

based on that criteria
i made some amendments to the formula
439 shows as 3 now

used column F again just so the formula looks neater to troubleshoot
if the final formula you can incorprate the weekend() bit into the if formula

Hi, one slight question

Is it possible to ignore the 'join if 1 and 5 are together' if days 1 and 5 have at least 5 days between them ?
For example, the first employee 88 is saying 1 period when it should be 2, because it is treating the last 2 absences (1 Nov 13 & 25 Nov 13 ) as linked due to both being day1 and day5, when in fact they are days apart. Does this make sense?

[humdingaling]

i added an additional check

if end day is 5 AND start day next line is 1 AND if end day-start day next line is 3 [this means its linked week) then ignore

[dele]

i added an additional check

if end day is 5 AND start day next line is 1 AND if end day-start day next line is 3 [this means its linked week) then ignore

great, I think you've nailed it! thanks again