which formula works

New Contributor

B/H

MondayTuesdayWednesdayThursdayFridayTOTAL HOURSSICKANNUAL LEAVETotal hrs remaining
09:00 - 17:00sick09:00 - 17:0009:00 - 17:0022.57.57.5278

 

Hi I'm new to excel i am trying to do a formula that would automatically put 7,5 hours in the sick column if someone went sick on a particular day  i got so far as =if(G1="SICK",7.5"0" 

It works for one day but if they were sick for the whole week i need it to total 37.5 hours anyone help please

 

6 Replies

@sammie9r 

=COUNTIF(A2:F2,"sick")*7.5

Is this the forumla you are looking for? 

Hi Nearly there but the total for one weeks sick should be 37.5 hours 7,5 hours per day not 67.5 hours

@sammie9r 

=COUNTIF(A2:E2,"sick")*F2

Please try formula above.

Thank you i also need the annual leave column to do the same thing but you have bank holiday and A/L how do you get them both to work in the same cell

@sammie9r 

=278-COUNTIF(A2:E2,"A/L")*F2

I assume that a day A/L reduces the Annual Leave by 7,5 hours and a bank holiday doesn't.

 

 

Hi i entered =COUNTIF(C16:K16,"sick")*7.5 and it came up with 60 hours