Work schedule time calculation containing TEXT string problem.

[tazmtiger]

Hi
I am working on an employee work schedule that works great, until boss decided he wants to use the following non time text in cells to it. Now I get a whole bunch of #VALUE! errors across all the rows containing text. On a row range B10:O10. total values for the row are in cells Q10 and R10.

I was wondering if I could make the use of the following text strings, and have excel igore them in any cells in mentioned range for corresponing row.

Text strings to ignore in ROW RANGE: "OFF" "KITCHEN" "N/O" and asign Zero or Blank Value to those text strings.

But, If Text string value is equal to "CLOSE" assign a numeric value of 11:00 PM to the that cell containing the text string "CLOSE". this formula string starts at column Q (Q10). And same concept repeats for columns: column S (S10), column U (U10), Column W (W10), column Y (Y10), column AA (AA10), column AC (AC10).

Here is the FORMULA string that I am using STARTING at cell Q10 and ENDING on cell AD10 from the same ROW.

IN / OUT Values.
in time --> Q10=((B10-INT(B10))*24)-((C10-INT(C10))*24) --> calculates IN hrs for Sunday
out time --> R10=IF(Q10<0,24+Q10,Q10) --> calculates OUT hrs for Sunday

in time --> S10=((D10-INT(D10))*24)-((E10-INT(E10))*24) --> calculates IN hrs for Monday
out time --> T10=IF(S10<0,24+S10,S10) --> calculates OUT hrs for Monday

in time --> U10=((D10-INT(D10))*24)-((E10-INT(E10))*24) --> calculates IN hrs for Tuesday
out time --> V10=IF(U10<0,24+U10,U10) --> calculates OUT hrs for Tuesday

in time --> W10=((D10-INT(D10))*24)-((E10-INT(E10))*24) --> calculates IN hrs for WED.
out time --> X10=IF(W10<0,24+W10,W10) --> calculates OUT hrs for WEDNESDAY

in time --> Y10=((D10-INT(D10))*24)-((E10-INT(E10))*24) --> calculates IN hrs for THURS.
out time --> Z10=IF(Y10<0,24+Y10,Y10) --> calculates OUT hrs for thursday

in time --> AA10=((D10-INT(D10))*24)-((E10-INT(E10))*24) --> calculates IN hrs for Friday
out time --> AB10=IF(AA10<0,24+AA10,AA10) --> calculates OUT hrs for Friday

in time --> AC10=((D10-INT(D10))*24)-((E10-INT(E10))*24) --> calculates IN hrs for Friday
out time --> AD10=IF(AC10<0,24+AC10,AA10) --> calculates OUT hrs for Friday

ANY HELP WITH THIS PROBLEM WILL BE GREATLY APRECIATED!

THANK YOU!

 

[DeBeuz]

Hi,

I tried this on a value in cell A1: =IF(OR(A1="OFF",A1="KITCHEN",A1="N/O"),0,IF(A1="CLOSE",TIMEVALUE("23:00"),A1))
Does this work for you?

Paul