Forum Discussion

mkissel1's avatar
mkissel1
Copper Contributor
Jan 07, 2023
Solved

How to associate a word with 0 hours

I am trying to make a work schedule. I am not good with Excel. I am using an excel sheet that allows me to calculate the total hours worked for each person for that week. The only issue I am having i...
excel
Excel on Mac
Formulas and Functions
  • Rodrigo_'s avatar
    Rodrigo_
    Jan 07, 2023

    Hi mkissel1 ,

    use IF and COUNTIF function to translate "OFF" to zero (0)
    =IF(COUNTIF($C4:$M4,"OFF")>=0,0,"insert your formula here")

Patrick2788's avatar
Patrick2788
Silver Contributor
Jan 07, 2023

mkissel1 

One possible solution is to enter 0 in the cell and then use custom cell formatting to show it as "OFF".  If the value is not 0 (or is text) it will be shown as normal.  Custom formatting is 'window dressing' so even though the cell may display "OFF", calculations will see the 0.

 

Custom cell formatting code: [=0]"OFF";[<>0]General

 

For example,

 

Resources