If Statement combining cell information

ladbroke

Board Regular
Joined
Jun 2, 2006
Messages
175
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to think of a way, badly, of combining information together.


hhu.png


As you can see, I have 5 staff members that each work a different shift pattern each week.
I want to see who gets certain double days off in a period of time.
In this example, I have 3 weeks worth of shifts that shows one person getting 2 Monday and Tuesdays off, some getting 1 and even 0.
I'd like my results to go in the cells B16-B20 as they are above.

I believe a combination of if, and, & or may be the best answer (based on a search here) but I can not figure out what to do.

Any help will be much appreciated.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,198
Office Version
  1. 2016
Platform
  1. Windows
Hi Ladbroke,

Does this work?

Please note that it does require an empty column after the last data column.

Cell Formulas
RangeFormula
B7:V7B7=TEXT(B8,"dddd")
C8:V8C8=B8+1
A16:A20A16=A9
B16:B20B16=SUMPRODUCT(($B$7:$V$7="Monday")*($B9:$V9="O")*($C9:$W9="O"))
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
You can use this user defined function.

Steps:
  1. Copy the code below into a VBA module in a copy of your workbook
  2. Enter the formula "=MonTueOff($B$8:$V$8,B9:V9)" without the quotes into cell B16
  3. Copy it down to cells B17:B20
VBA Code:
Function MonTueOff(days As Range, sel As Range) As Long
    Dim d As Variant
    Dim s As Variant
    Dim i As Integer
    Dim c As Long
    
    If sel.Rows.Count <> 1 Then
        MonTueOff = "#ERROR! Select only 1 row of data."
        Exit Function
    End If

    If days.Rows.Count <> 1 Then
        MonTueOff = "#ERROR! Select only 1 row for days of the week."
        Exit Function
    End If

    If days.Columns.Count <> sel.Columns.Count Then
        MonTueOff = "#ERROR! Select the same number of columns for days and data."
        Exit Function
    End If

    d = days.Value
    s = sel.Value
    c = 0
    
    For i = 1 To sel.Columns.Count - 1
        If Weekday(d(1, i), vbMonday) = 1 And s(1, i) = "O" And Weekday(d(1, i + 1), vbMonday) = 2 And s(1, i + 1) = "O" Then
            c = c + 1
        End If
    Next i
    
    MonTueOff = c
End Function
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,841
Office Version
  1. 365
Platform
  1. Windows
Please note that it does require an empty column after the last data column.
Or you could just shorten each of your ranges by one column since it is impossible to have a 2-day happening starting with the last column of data.

@ladbroke
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Microsoft 365 (& assuming real dates - numbers - in row 8) you could also try this.

21 07 16.xlsm
ABCDEFGHIJKLMNOPQRSTUV
7MondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
812/07/202113/07/202114/07/202115/07/202116/07/202117/07/202118/07/202119/07/202120/07/202121/07/202122/07/202123/07/202124/07/202125/07/202126/07/202127/07/202128/07/202129/07/202130/07/202131/07/20211/08/2021
9FredOOELEWLOWEHLOOEWQLTOO
10DenisLLOLEWLOWEHLLOEWQLTOO
11SandraOOLLEWLOOEHLOOOOQLTOO
12RichardEWOLEWLOOEHLOWEWQLTOO
13DanielOOLLEWLOOEHLOOOlQLTOO
14
15Monday & Tuesday Off Combo
16Fred1
17Denis0
18Sandra3
19Richard1
20Daniel2
Days Off
Cell Formulas
RangeFormula
B16:B20B16=COUNT(FILTER(B$8:U$8,B$7:U$7&B9:U9&C9:V9="MondayOO"))
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Or you could just shorten each of your ranges by one column since it is impossible to have a 2-day happening starting with the last column of data.
@Peter_SSs - which is exactly what I did in my user defined function :) GMTA
 

Forum statistics

Threads
1,148,190
Messages
5,745,246
Members
423,938
Latest member
manilaphilips

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top