Excel formula gurus please

Yella Fella · 28 Apr 2022 at 16:34

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.

Yella Fella · 4 May 2022 at 09:49

I couldn't get the weekday function to work so I manually entered Friday. This is the full working formula for me:

=SWITCH('2022'!G12,"a","A","r","R","h","H",IF(G$4="Fri",$C$12,$B$12))

Yella Fella · 4 May 2022 at 09:49

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 · 4 May 2022 at 12:24

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.

Yella Fella · 4 May 2022 at 12:27

gpuerrilla said:
Is there one person updating the sheet or is this information coming from elsewhere?

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 <>""

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.