which formula works

New Contributor


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



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



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



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