issue with error #Value!

[bestdad]

Ok I am using a formula that creates a schedule. if the person is off that day no hours is shown giving me a if statement that is false so to hide the false i am using "". I would like it to state "OFF"

Formula #1 in cell c8 worksheet wk-1 schedule "please remember that we are dealing with time in these two formulas"
Time IN
=IF(COUNTIF(E8:S8,1),INDEX($E$6:$S$6,MATCH(1,E8:S8,0)),"")
Time Out
=IF(COUNTIF(E8:S8,1),INDEX($E$6:$S$6,MATCH(2,E8:S8,1)),"")

I am coping the output to a new worksheet. to build the schedule the employees see.

Called: worksheet Schedule
I have the days of the week. each day has a time in cell and a time out cell.
at the end it calculates the total hrs and hrs left the employee could potentially be assigned.

Here is the problem: Because my if statements is false if they are blank so i hide it with "". my calculations wont work on the next page giving me #value! error issue
Formula being used:
=((D9-C9)+(F9-E9)+(H9-G9)+(J9-I9)+(L9-K9)+(N9-M9)+(P9-O9))*24
and
='Employee List'!C9-Q9

 

[shift-del]

Hello

Here is the problem: Because my if statements is false if they are blank so i hide it with "". my calculations wont work on the next page giving me #value! error issue

"" is text. Replace the "" with 0.
To hide a 0: Display of hide zero values

 

[bestdad]

Hello

"" is text. Replace the "" with 0.
To hide a 0: Display of hide zero values

Problem with that is if I change "" to zero since i am working with time excel changes 0 to 12am in the morning. because we are working with hrs on the clock.

 

[Marcelo Branco]

Maybe this Array-formula

=SUM(IF(ISNUMBER($C9:$P9),IF(MOD(COLUMN($C9:$P9)-COLUMN($C9)+1,2)<>0,-$C9:$P9,$C9:$P9)))

confirmed with Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

oops...

forgot to multiply by 24

=SUM(IF(ISNUMBER($C9:$P9),IF(MOD(COLUMN($C9:$P9)-COLUMN($C9)+1,2)<>0,-$C9:$P9,$C9:$P9)))*24

Ctrl+Shift+Enter

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter...

=SUM(24*(IF(MOD(COLUMN($D9:$P9)-COLUMN($D9),2)=0,
    IF(ISNUMBER($D9:$P9),$D9:$P9))-IF(MOD(COLUMN($C9:$O9)-COLUMN($C9),2)=0,
      IF(ISNUMBER($C9:$O9),$C9:$O9))))

in order to cover:

=((D9-C9)+(F9-E9)+(H9-G9)+(J9-I9)+(L9-K9)+(N9-M9)+(P9-O9))*24


And...

=N('Employee List'!C9)-N(Q9)

in order to cover:

='Employee List'!C9-Q9

 

[mikerickson]

Switch the "" and the 0 and format the cell with the custom format
[=0]"";hh:mm:ss or [=0]"";h:mm AM/PM

which shows 0 as blank (even though the underlying value is 0 and it can be added)
and all others in Time format.

 

[bestdad]

Thanks ahead of time for all the replys. I will have to try to implement them and let you know in the next day or so. Thanks again.