Help with excel spreadsheet please

vix2000

Can anyone experienced in excel help please?  I am quite au fait with excel and formulas but this I am finding out of my experience.  I am trying to create a spreadsheet for one person to calculate daily sick leave in a rolling 12 month period, giving the remaining entitlement from an allowance of 112 days per 12 month period.  I know as the first day of the period drops off it is added to the last day but I have no idea how to formulate this.  If anyone can help me with this I would be grateful.

GDB2222

How is the data recorded?

Vitor

Suggest clearly explaining what you want to ChatGPT

Heedtheadvice

As GDB writes more info on how the sick leave is recorded is required to answer the question.

Recording data on a daily basis ( or half days if that is the period of sick leave that is accumulated) can be great. It allows calculations such as you request or days of the week or other patterns or recording of events that might have triggered sick leave such as accidents and charting that leave or even recording 'annual leave' in the same record. 

That can be done as columns of data ( date, am/pm, person, leave type, incident, location etc.) with a row for headings and then the data.

 Excel can record a massive number of rows so potential for many data sets ( people, work areas/locations etc.) all on one sheet.

Better suggestions once you respond perhaps giving sample data.

A word of caution, do be aware of the requirements of GDPR  as you are probably holding personal data!!

vix2000

This is for my partners personal use.  The sick leave is calculated on a daily basis, the allowance is 16 weeks on a 12 month rolling basis.  The aim is a formula to calculate how many days are remaining on a rolling basis. Thank you .

tizerbelle

so quick, inelegant solution is in column A record all sick days in date format (don't include non-working days)

Cell C1: =TODAY()
Cell C2: =C1-365
Cell C3: =COUNTIFS(A:A,">="&C2,A:A,"<="&C1)

Heedtheadvice

Using the above and adding two dates into column A gives answer 2...... so that is the correct number of sick leave days but not how many days in the period remain. A bit more is required.

If we assume a 5 day week (which  may not be true such as for shift working?)

Add into  cell c4 = 16*5

and C6 =(C4-C3)/5 gives what Vix is looking for?

i.e. for two days sick days off work in a 5 day working week in last ROLLING year where max allowance is 16 weeks.

However 112 days allowance were mentioned in opening post so Cell C4 need to be changed depending upon the requirement.......

tizerbelle

Or simply Cell C4: =112-C3

vix2000

Thank you for all the help. The 3 waiting days have to be on shift days but the 4 months sick pay includes weekends and bank holidays as it is a 4on 4off shift pattern. I will have a play with the above when I get home. Thanks again

vix2000

Again thank you for the advice.
So this is the 3 columns I had envisaged in my simple spreadsheet.  As above the 16 weeks sick allowance is full weeks, so every day is recorded in column A.  In Column B a sick day taken is indicated by 1. Do I need an extra column to total how many sick days up to date? (If so how do I calculate the days dropping off as outside the 52 week period?) then column D would calculate any sick days remaining.  Is this possible?

vix2000

here is my basic spread sheet