title: Excel - Nested IF/Nested AND/OR in 'calculated field' option - pivot table
body: <p>I have the following problem:</p>
  
  <p>A datasheet with a column (HOUR) and another column (AM/PM). Entries in the first column consist of 1,2,3,4,5,6,7,8,9,10,11, or 12, the second column consists of 'AM's or 'PM's. Together they define the time of an incident (regarding the below problem, note that I am not allowed to create a new column in the source datasheet or change existing columns). The below formulas 1.) to 3.) work excellent for getting '1's or '0's for incidents that happened either between 8AM and 4PM, or outside of this time window, as long as I create a new column somewhere.</p>
  
  <pre><code>1.) =IF(AND(A1&gt;=8, A1&lt;=11),IF(B1="AM",1,0),0) + IF(AND(A1&gt;=1, A1&lt;=4),IF(B1="PM",1,0),0) + IF(AND(A1=12),IF(B1="PM",1,0),0)
  
  2.) =--OR(AND(A1&gt;=8, B1="AM", A1&lt;&gt;12), AND(OR(A1&lt;=4, A1=12), B1="PM"))
  
  3.) =--OR(AND(OR(A1={8,9,10,11}),B1="AM"), AND(OR(A1={1,2,3,4,12}), B1="PM"))
  </code></pre>
  
  <p>However, I want the "1"s  to be summarized - without creating an extra column - as calculated field in a pivot table. While excel doesn't accept the 3.) formula at all in the calculated field option, excel accepts 1.) and 2.), but puts out only "0"s in all pivot cells. The below is one of the formulas that puts out only "0"s in the pivot table.</p>
  
  <pre><code> =--OR(AND(HOUR&gt;=8,'AM/PM'="AM",HOUR&lt;&gt;12), AND(OR(HOUR&lt;=4,HOUR=12),'AM/PM'="PM"))
  </code></pre>
  
  <p>The field value settings don't make a difference, and the fields that are created new with 1.) or 2.) cannot be filtered for "1"s or 0"s, so something must be wrong with the field calculation I guess. Does anybody know what I need to change to make it work? Are there special rules for formulas in pivot tables that apply to formula 1.) and 2.) to make them work?</p>
  
  <p>Thanks for any help on this </p>