Excel formula gurus please

Yella Fella

Yella Fella

Yella Fella

Associate
Joined
18 Apr 2004
Posts
335
Location
Milton Keynes, UK
Hey all, I'm trying to work formula to see if a staff was in, it will show their shift hours if its Mon-Thu or Fri. I've listed the shift times in 2 columns.

I'm using as an example, =TEXT(F5,"ddd") to display the weekday. So the following formula works where it checks conditional formatting on another sheet within the same workbook whether if someone is, to keep it simple, absent with code A or holiday with code H.

I've changed it from multiple nested IF into one switch. The logic seems fine but it's not working still, getting a #VALUE! still. Anyone able to help?

=IF(AND(WEEKDAY(F4)=6,SWITCH('2022'!G12,"a","A","r","R","h","H",$B$12)),$C$12)

The absolute reference is the shift hours depending on if it's Mon-Thu or Fri. Many thanks.
 
Without being at a machine to test, the first thing that looks 'off' is your AND formula. The first argument makes sense (if the weekday is equal to 6) as that will evaluate to true or false, the second statement however (the switch) will return A, R, H or the value of B12. These aren't expressions that evaluate to true or false.

AND needs 1/TRUE and 0/FALSE to work.

1 1 evaluates to true
1 0 evaluates to false
0 1 also false
0 0 also false
 
On another note. I'm trying to figure out how to work out how many '1's and other numbers are listed in 5 rows of 5 cells. Then to count that number up.

Trying to use countif or perhaps sumif is better. Just working out the logic for this.
 
Yella Fella said:
On another note. I'm trying to figure out how to work out how many '1's and other numbers are listed in 5 rows of 5 cells. Then to count that number up.

Trying to use countif or perhaps sumif is better. Just working out the logic for this.
Click to expand...
Use sumifs and countifs, they're something added in the last few years and make this much easier when you have multiple criteria.

Your AND formula is not right though.

Why use SWITCH, just use UPPER if you want to capitalise the letter in there. But I still dont see the need? Just check that the cell <>""
 
Hard to explain without showing though I give it a go. I also posted this on reddit at the time when someone suggested the SWITCH statement as I had multiple nested IF statements.

I have the main staff rota which shows the shift patterns of staff starting between 08:00-09:00 and has codes for if someone is off either sick/holiday (using conditional formatting). I replicated this rota on another tab and use it primarily for working out stats for worked hours and hours off.

Hours are consistent Mon-Thu and Fri the hours are reduced. My logic was unless staff was off, it will list the corresponding hour they should be working Mon-Thu unless on Fri then list the shorter hours. If not worked it will list A for absent or H for holiday. At the bottom of this I tally up total worked hours, total sick hours and total holiday hours. This in turn gets put into another daily/weekly stats report.

At the moment in post #3 this works for me in the main section checking the said days, if worked, if off.
 
gpuerrilla said:
Is there one person updating the sheet or is this information coming from elsewhere?
Click to expand...
Management and myself. Mainly me however... I update on the main rota who's off. The shift is always set.

wesimmo said:
Use sumifs and countifs, they're something added in the last few years and make this much easier when you have multiple criteria.

Your AND formula is not right though.

Why use SWITCH, just use UPPER if you want to capitalise the letter in there. But I still dont see the need? Just check that the cell <>""
Click to expand...
Different formula now. For the other spreadsheet, I ended up using COUNTIF and adding each up as they were set over 5 rows of 5 cells rather than columns. Again that worked though seems longwinded.
 
You must log in or register to reply here.
Back
Top Bottom