12 month rolling calculation

Hi

I'm hoping someone can help.

I am trying to create a sickness absence management spreadsheet which calculates the total number of days absent in a rolling 12 month period.

The spreadsheet will be for an individual.

So far I have headings for columns A-C starting in row 4

Column A - Start date of sickness Column B - End date of sickness Column C - Total no. of days

With the total number of days I want to be able to have a running total B2, with today's date on A2.

Can anyone help me with this.

Thank you so much in advance.

A sample as per the banner would help

Other questions are, if sickness spans the last year eg if someone was sick 1-nov-18 to 1-dec-18 how many days are to be calculated in the last 12 months? Do weekends count?

See attached file.

I thought my formula in B2 in such a way that if you input any date (not necessarily today) in cell A2, it would calculate the total number of days up to that day.
In the attached file, instead of adding =TODAY() in A2, I added today - 05.11.2019 manually, to better point out the date being in an interval in ranges A and B.

It's probably not the best solution, but it's one that returns the correct value.

Originally Posted by Mrrrr

See attached file.

I thought my formula in B2 in such a way that if you input any date (not necessarily today) in cell A2, it would calculate the total number of days up to that day.
In the attached file, instead of adding =TODAY() in A2, I added today - 05.11.2019 manually, to better point out the date being in an interval in ranges A and B.

It's probably not the best solution, but it's one that returns the correct value.

Thank you! I will try it out.

You have not answered my questions, the return may work be debony, but does not account for a period of sickness at the beginning of the year, or if the file contains say 3 years of sickness records.


It depends how you capture your information

True.

It may not be an OK solution after all. It all depends on what debony needs.