Excel gurus - rolling 365-day formula

[Yella Fella]

Hey all. So I have to work out my staff's sickness days and calculate their Bradford Factor score. However, this is done on a rolling 365 days from the day I work this out.

I tried searching online but most of it was setting up rolling calendar dates in the future. I need a way of working this out prior.

I've sorted a date column and each staff have their own column. I've just put a 1 in the cell for when they have been off and calculated it manually. Any ideas of where to start with a formula for this if possible? Cheers.

 

[wesimmo]

I assume you have dates in column A, then across the rows you have the employees?

If so, look into the use of SUMIFS. You'll need to put a SUMIFS in, probably at the top of the column for each employee would be easiest.

Then use 2 criteria, date range <=TODAY() and date range >TODAY()-365.

 

[TheCSSDoctor]

As above, assuming you just have dates in column A, then your employee names in columns B to whatever, you could insert a blank row above the names and then add this formula in column B for the first person, then just drag it across for each employee and it will update accordingly

=SUMIFS(B:B,$A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-365)

You can then just add future dates to the bottom of the list of dates and freeze the panes at the top to keep the employee name and number of days visible, or create a summary sheet with the names and totals on to make it neater if you want to view a summary

 

[Yella Fella]

Thanks all. Yes, that's correct with Date in column A, Staff 1 in B, Staff 2 in C and so on... I'll give this a go and have a look at how this works.

I'll attempt to make it dynamic after as well when putting new dates in. Cheers.

 

[Yella Fella]

As above, assuming you just have dates in column A, then your employee names in columns B to whatever, you could insert a blank row above the names and then add this formula in column B for the first person, then just drag it across for each employee and it will update accordingly

=SUMIFS(B:B,$A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-365)

You can then just add future dates to the bottom of the list of dates and freeze the panes at the top to keep the employee name and number of days visible, or create a summary sheet with the names and totals on to make it neater if you want to view a summary

If I was to use letters to donate absence e.g. "A" would I need to incorporate a COUNTA somewhere for this? At the moment this works if it's a 1 in the cell.

Separately I would like to work out of which days is covid related as it doesn't count towards the Bradford Factor score... e.g. staff is off for 20 days throughout the year, of which 5 of those is covid. Therefore they are off for 20 days but only using 15 days to calculate as the Bradford Factor.

 

[TheCSSDoctor]

If I was to use letters to donate absence e.g. "A" would I need to incorporate a COUNTA somewhere for this? At the moment this works if it's a 1 in the cell.

Separately I would like to work out of which days is covid related as it doesn't count towards the Bradford Factor score... e.g. staff is off for 20 days throughout the year, of which 5 of those is covid. Therefore they are off for 20 days but only using 15 days to calculate as the Bradford Factor.

Off the top of my head I think you can almost substitute the SUMIFS with COUNTIFS - not at my PC at the moment so can't quickly check...think it should be similar, but you would need additional criteria to specify which letter to count towards the total

 

[TheCSSDoctor]

If I was to use letters to donate absence e.g. "A" would I need to incorporate a COUNTA somewhere for this? At the moment this works if it's a 1 in the cell.

Separately I would like to work out of which days is covid related as it doesn't count towards the Bradford Factor score... e.g. staff is off for 20 days throughout the year, of which 5 of those is covid. Therefore they are off for 20 days but only using 15 days to calculate as the Bradford Factor.

Ok, back at my desk and had a quick check

You can use this formula to count up the number of cells where the value is 'A' (the last variable in the formula below)

=COUNTIFS($A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-365,B:B,"A")

The first 2 criteria are referencing the dates, and the final bit of 'B:B, "A"' is checking the staff member to see if the column contains the value "A" - you could even change the value to search for to say 'Absent' and you could have another formula to count the number of days for COVID if you wanted, just change the final variable from "A" to "C" or whatever you wanted it to be

 

[wesimmo]

I'd do the above COUNTIF for each absence type at the top of the column, so using the same formula but changing the "A" to whatever possible codes you have.

Then summarising those into groups using a simple SUM would probably be easiest for a non Excel geek.

Edit: @TheCSSDoctor can you remember when we were lads and SUMIFS and COUNTIFS hadn't been invented? Kids today, dont know how lucky the are.

 

[Yella Fella]

I'd do the above COUNTIF for each absence type at the top of the column, so using the same formula but changing the "A" to whatever possible codes you have.

Then summarising those into groups using a simple SUM would probably be easiest for a non Excel geek.

Edit: @TheCSSDoctor can you remember when we were lads and SUMIFS and COUNTIFS hadn't been invented? Kids today, dont know how lucky the are.

Thanks for this all @TheCSSDoctor and @wesimmo. I couldn't get the single COUNTIF to work so had to do two separate instances one working the overall number of days off as A and the other for C. I like to work out what you all did to understand how it's all put together. I do mainly staffing/budget stuff around staffing so all this helps and I'm sure I reuse parts in future.

Other than using scripting, the next hard part... is there a way of working out the number of occurrences? e.g. staff 1 has taken 15 days off sick, but over 3 occurrences of 5 days. This is needed to work out the Bradford Factor score.

 

[wesimmo]

Thanks for this all @TheCSSDoctor and @wesimmo. I couldn't get the single COUNTIF to work so had to do two separate instances one working the overall number of days off as A and the other for C. I like to work out what you all did to understand how it's all put together. I do mainly staffing/budget stuff around staffing so all this helps and I'm sure I reuse parts in future.

Other than using scripting, the next hard part... is there a way of working out the number of occurrences? e.g. staff 1 has taken 15 days off sick, but over 3 occurrences of 5 days. This is needed to work out the Bradford Factor score.

Hmmm occurrences is going to get very technical!

But, as for how I learned to use Excel etc, basically 25 years of experience doing exactly what you're doing now, knowing there must be a way and then Googling it.

MrExcel.com used to be a good resource for learning.

Edit: quick thought, you could possibly take a copy of the sheet once you have the structure in place.

Then, instead of entering the absence manually you put a formula in the second sheet that brings back the absence in the first sheet only if the cell above it on the first sheet is blank. That way a series of consecutive days would only appear on the secondary sheet once, and your COUNTIFS at the top would give you occurrences and not total days.

The problem with this is that you have to make sure the sheets are exactly the same structure so if you're updating staff lists often then that becomes a risk.

 

[TheCSSDoctor]

I'd do the above COUNTIF for each absence type at the top of the column, so using the same formula but changing the "A" to whatever possible codes you have.

Then summarising those into groups using a simple SUM would probably be easiest for a non Excel geek.

Edit: @TheCSSDoctor can you remember when we were lads and SUMIFS and COUNTIFS hadn't been invented? Kids today, dont know how lucky the are.

Haha, yeah I remember ye olde days when we didn't have all these fancy formula to help out with stuff

Thanks for this all @TheCSSDoctor and @wesimmo. I couldn't get the single COUNTIF to work so had to do two separate instances one working the overall number of days off as A and the other for C. I like to work out what you all did to understand how it's all put together. I do mainly staffing/budget stuff around staffing so all this helps and I'm sure I reuse parts in future.

Other than using scripting, the next hard part... is there a way of working out the number of occurrences? e.g. staff 1 has taken 15 days off sick, but over 3 occurrences of 5 days. This is needed to work out the Bradford Factor score.

If you want a breakdown on how that formula I gave you works, just say and I can explain it - I have had to do similar for my wife at times for her work, breaking down different parts of the formula to explain it a bit clearer, for example, the first time she wanted to use a VLOOKUP formula I explained how each part made it up (what are you looking for, where do you want to look for it, what do you want to pull back from it)

The next part you are asking for is something I would have to give some thought to - I suspect it could be done using just formula but I would probably have a hidden sheet in the background with all the bits you would need on it to do the calculations

 

[Yella Fella]

Hmmm occurrences is going to get very technical!

But, as for how I learned to use Excel etc, basically 25 years of experience doing exactly what you're doing now, knowing there must be a way and then Googling it.

MrExcel.com used to be a good resource for learning.

Edit: quick thought, you could possibly take a copy of the sheet once you have the structure in place.

Then, instead of entering the absence manually you put a formula in the second sheet that brings back the absence in the first sheet only if the cell above it on the first sheet is blank. That way a series of consecutive days would only appear on the secondary sheet once, and your COUNTIFS at the top would give you occurrences and not total days.

The problem with this is that you have to make sure the sheets are exactly the same structure so if you're updating staff lists often then that becomes a risk.

I'll have a look at this. I have something similar with the actual staff rota to work out the visual part up front and the stats part at the back based on what is showing up front.

Same again for my overtime rota. In this particular section I just couldn't figure out how to work out the 365-rolling day summing. I don't usually use the date/today formulas.

 

[Yella Fella]

Haha, yeah I remember ye olde days when we didn't have all these fancy formula to help out with stuff

If you want a breakdown on how that formula I gave you works, just say and I can explain it - I have had to do similar for my wife at times for her work, breaking down different parts of the formula to explain it a bit clearer, for example, the first time she wanted to use a VLOOKUP formula I explained how each part made it up (what are you looking for, where do you want to look for it, what do you want to pull back from it)

The next part you are asking for is something I would have to give some thought to - I suspect it could be done using just formula but I would probably have a hidden sheet in the background with all the bits you would need on it to do the calculations

Yes please. Other than googling and asking online, I tend to like looking at what/how it's done and break it down to understand the elements. All training with be much appreciated if you can explain the formula breakdown.

I can obviously see the elements in Excel as it shows range, criteria etc. Gets well complex with a lot of nested formulas as well.

 

[Yella Fella]

If it helps... I did have a VB script to work out the occurrences which does work on one test sheet but I can't get it to work on the sheet I actually want to report in . Not sure if it has to be written or put into a particular sheet/module. New to the coding side:

Option Explicit

Function Occurrences(daterange As Range, coderange As Range, code As String)
    Dim i As Long
    Dim f As Boolean
    For i = 1 To daterange.Count
        If Weekday(daterange(i).Value, vbMonday) <= 5 Then
            If coderange(i).Value = code Then
                If Not f Then
                    Occurrences = Occurrences + 1
                    f = True
                End If
            Else
                f = False
            End If
        End If
    Next i
End Function

 

[wesimmo]

That makes it easier if you can use a custom function.

If you go into Developer>>Visual Basic

Then right click your spreadsheet in project tree and Insert Module.

Paste your code into the Module1 it creates and that should then work.

 

[TheCSSDoctor]

Yes please. Other than googling and asking online, I tend to like looking at what/how it's done and break it down to understand the elements. All training with be much appreciated if you can explain the formula breakdown.

I can obviously see the elements in Excel as it shows range, criteria etc. Gets well complex with a lot of nested formulas as well.

Ok, I will try to explain it the best I can

Formula - =COUNTIFS($A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-365,B:B,"A")

The COUNTIFS allows you to build up a number of conditions to pull back results and is built up with however many criteria you want, but the more you add, the more complicated it can get, so the one above is fairly simple relatively speaking as it only has 3 criteria to check

Criteria 1 - $A:$A,"<="&TODAY()

Using your sheet with the dates in column A and then staff in columns B to ... (however many staff you have)

The first part of the criteria is saying use column A (the entire column, not a specific number of rows) and is locked to prevent it changing if you copy the formula to another column
The second part of the criteria is checking to see if the date in the cell in column A is today or a date prior to today (this is how you can use rolling date periods)
You need to use double quotes when using parameters such as those used in this formula and then use '&' to add on the wording - if you were to use the TODAY function as part of the formula in a cell it would simply be <=TODAY() without the quotes

Criteria 2 - $A:$A,">"&TODAY()-365

This is pretty much the same as the first part of the formula, but is instead looking for a date that is between today and 365 days ago (again, how you can use a rolling date period)

Criteria 3 - B:B,"A"

This final bit of the formula is looking in the column of the staff member (in this case, the staff member in column B) and looking for where there is a value of 'A' in the cell (this could be changed to whatever letter or word you want)
As this reference of column B is not locked, when you copy this formula to column C / D / E / etc, that part of the formula will change to reflect the column it is copied to, but the locked references earlier in the formula will still reference column A

So if you copied the formula to column C for the next staff member, it would look like this -

=COUNTIFS($A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-365,C:C,"A") and so on

When combining all elements of the formula, it is counting up all instances in the staff column where the date falls between today AND 365 days ago AND where the cell value is equal to 'A'

If you kept this sheet with only the dates and staff columns on it, by using the TODAY() function you can just keep adding dates to the bottom of the list until you reached the limit of the spreadsheet (something like just over 1 million rows) without ever having to change the formula to reference a specific time period

Hope that makes sense

 

[wesimmo]

The use of $ to fix references is one of the fundamentals you need to get your head round when building big models.

Being able to copy and drag formulas and knowing whether the columns, rows or both are relative or if they're fixed when you do so will be something you will use a dozen times a day if you're doing serious spreadsheeting.

 

[Yella Fella]

That makes it easier if you can use a custom function.

If you go into Developer>>Visual Basic

Then right click your spreadsheet in project tree and Insert Module.

Paste your code into the Module1 it creates and that should then work.

Is it correct to say if doing this report in e.g. sheet 3 but the data is in sheet 1, you can't reference VBA scripts in sheet 3?

But have to reference it properly in VBA?

 

[wesimmo]

No, you can reference another sheet in the range part of the formula, a custom function works just like an inbuilt function in that respect.

So I've just written a little custom function that counts non blank cells, called ws.

I've then got a sheet of data called other_sheet and then a 'report sheet'.

In the report sheet I can put
=ws(other_sheet!A1:A6)
and it will count the non blanks in that range on that sheet.

 

[Yella Fella]

Ok, I will try to explain it the best I can

Formula - =COUNTIFS($A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-365,B:B,"A")

The COUNTIFS allows you to build up a number of conditions to pull back results and is built up with however many criteria you want, but the more you add, the more complicated it can get, so the one above is fairly simple relatively speaking as it only has 3 criteria to check

Criteria 1 - $A:$A,"<="&TODAY()

Using your sheet with the dates in column A and then staff in columns B to ... (however many staff you have)

The first part of the criteria is saying use column A (the entire column, not a specific number of rows) and is locked to prevent it changing if you copy the formula to another column
The second part of the criteria is checking to see if the date in the cell in column A is today or a date prior to today (this is how you can use rolling date periods)
You need to use double quotes when using parameters such as those used in this formula and then use '&' to add on the wording - if you were to use the TODAY function as part of the formula in a cell it would simply be <=TODAY() without the quotes

Criteria 2 - $A:$A,">"&TODAY()-365

This is pretty much the same as the first part of the formula, but is instead looking for a date that is between today and 365 days ago (again, how you can use a rolling date period)

Criteria 3 - B:B,"A"

This final bit of the formula is looking in the column of the staff member (in this case, the staff member in column B) and looking for where there is a value of 'A' in the cell (this could be changed to whatever letter or word you want)
As this reference of column B is not locked, when you copy this formula to column C / D / E / etc, that part of the formula will change to reflect the column it is copied to, but the locked references earlier in the formula will still reference column A

So if you copied the formula to column C for the next staff member, it would look like this -

=COUNTIFS($A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-365,C:C,"A") and so on

When combining all elements of the formula, it is counting up all instances in the staff column where the date falls between today AND 365 days ago AND where the cell value is equal to 'A'

If you kept this sheet with only the dates and staff columns on it, by using the TODAY() function you can just keep adding dates to the bottom of the list until you reached the limit of the spreadsheet (something like just over 1 million rows) without ever having to change the formula to reference a specific time period

Hope that makes sense

Thanks for the explanation. I understood a fair bit but sometimes it's working out the logic in your head and testing it as well. That's what I do anyways with trial and error at times once I've figured some of the elements out.

I find the date formulas interesting so look forward to seeing what else I can do with that.