We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Strange EXCEL request!!
Options
truescot
Posts: 193 Forumite
in Techie Stuff
Looking for help from Excel wizzkids:
I have designed a spreadsheet for shift rotas at work. I am looking to add an at-a-glance way of seeing how many weekends off each of my employees has in a four week rota.
The formula would be at the end of each employees row on the sheet and would show me how many sets of Fri/Sat/Sun (inclusive) off everyone has. ie. only Friday/Saturday AND Sunday off would count as a full weekend off. I Googled it and used Help but can't seem to work out how to do it.
A day off is just a blank cell on the spreadsheet.
Is this possible?
Ta x
I have designed a spreadsheet for shift rotas at work. I am looking to add an at-a-glance way of seeing how many weekends off each of my employees has in a four week rota.
The formula would be at the end of each employees row on the sheet and would show me how many sets of Fri/Sat/Sun (inclusive) off everyone has. ie. only Friday/Saturday AND Sunday off would count as a full weekend off. I Googled it and used Help but can't seem to work out how to do it.
A day off is just a blank cell on the spreadsheet.
Is this possible?
Ta x
Skint: (adjective) The tendency to turn off the grill when turning the bacon.
Think skint - it makes things simpler
Think skint - it makes things simpler
0
Comments
-
Countif .0
-
Sorry to be really stupid, but I thought it would be COUNTIF, as I have used it elsewhere in the spreadsheet. I just cant work out a way for it to count a blank Friday/Sat AND Sun as 1. Counting up the total F/S OR S which are blank is not what I want to do (I have done that on the spreadsheet already). I need to count the groups to see the weekends off, so the AND is the key part?!?!.
Sorry if it is really simple, but if you can direct me to somewhere that explains it...Skint: (adjective) The tendency to turn off the grill when turning the bacon.
Think skint - it makes things simpler0 -
Possible method is use a hidden column and set it using IF((isblank f/s and isblank su) ,1,0) then countif, sorry being vague, can't get on my Windows m/c from here.4.8kWp 12x400W Longhi 9.6 kWh battery Giv-hy 5.0 Inverter, WSW facing Essex . Aint no sunshine ☀️ Octopus gas fixed dec 24 @ 5.74 tracker again+ Octopus Intelligent Flux leccy0
-
I'm fairly sure excel has a countblanks function
2 mins
Owner of andrewhope.co.uk, hate cars and love them
Working towards DFD
HSBC Credit Card - £2700 / £7500
AA Loans - (cleared £9700)0 -
Try this formula at the end of your row
=NOT(SUM(A6:C6))+NOT(SUM(H6:J6))
A6:C6 is the first weekend and so on. Just add more +not(sum(range)) terms as required.
If the weekend cells have non-numerical values or zero it is counted!
If the weekend cells contain numbers (hours worked) then the weekend is not counted.
HTH, Dave0 -
Nvm looks as already reply above Owner of andrewhope.co.uk, hate cars and love them
Working towards DFD
HSBC Credit Card - £2700 / £7500
AA Loans - (cleared £9700)0 -
Try this formula at the end of your row
=NOT(SUM(A6:C6))+NOT(SUM(H6:J6))
A6:C6 is the first weekend and so on. Just add more +not(sum(range)) terms as required.
If the weekend cells have non-numerical values or zero it is counted!
If the weekend cells contain numbers (hours worked) then the weekend is not counted.
HTH, Dave
Thanks Dave, only problem being the sheet shows N or D for Night or Day shift, not hours worked. Any suggestion with this in mind? Appreciate itSkint: (adjective) The tendency to turn off the grill when turning the bacon.
Think skint - it makes things simpler0 -
A way of doing it would be using a user defined function. The following should do the trick:
Option Explicit
Public Function CountWeekendsOff(ByRef DateRow As Range, ByRef RotaRow As Range) as long
Dim c, d As Long
Dim BlankDays, BlankWeekends As Long
If DateRow.Columns.Count <> RotaRow.Columns.Count Then
CountWeekendsOff = "#Error - ranges must have some number of cells)"
Exit Function
End If
If DateRow.Rows.Count <> 1 Or RotaRow.Rows.Count <> 1 Then
CountWeekendsOff = "#Error - ranges must have only one row)"
Exit Function
End If
BlankWeekends = 0
For c = 1 To DateRow.Columns.Count
If Weekday(DateRow.Cells(1, c), vbMonday) = 5 Then 'it's a friday so check if the weekend is blank
BlankDays = 0
For d = 0 To 2
If (Not IsNumeric(RotaRow.Cells(1, c + d))) Or (RotaRow.Cells(1, c + d) = 0) Then BlankDays = BlankDays + 1
Next
If BlankDays = 3 Then BlankWeekends = BlankWeekends + 1
End If
Next
CountWeekendsOff = BlankWeekends
End Function
With this function you just need to put the following formula in a cell at the end of each row:
=CountWeekendsOff(B$1:V$1,B6:V6)
This assumes that you have a date header row (in this example in cells B1:V1) and then the second range (B6:V6) contains the details of the rota for the individuals' row.
If you've not familiar with user defined functions and would like to give this a try let me know and I'll explain how to set the function up.
I've tested it with a simple spreadsheet but I'm not sure how your one is structured - hopefully it'll work
Edit: Have just noticed that the post has screwed up the formating of the code a bit. If yoiu want the try this method then let me know and I'll have another go at posting the codeNever let it get you down... unless it really is as bad as it seems.0 -
Simplest answer would be to include a hidden column for each weekend with a formula along the lines of:
=If(concatenate(D6,D7)="",1,0)
which would would put a 1 if D6 and D7 (e.g. Friday and Saturday) are blank. These can then easily be totaled into a new column at the end.0 -
A way of doing it would be using a user defined function.
By the way, following on from my previous post, if the header row doesn't contain a date (but just say a weekday header) then the code can simply be changedNever let it get you down... unless it really is as bad as it seems.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply
Categories
- All Categories
- 350.9K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.5K Spending & Discounts
- 243.9K Work, Benefits & Business
- 598.7K Mortgages, Homes & Bills
- 176.9K Life & Family
- 257.2K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards