1

I have the following problem:

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.

1.) =IF(AND(A1>=8, A1<=11),IF(B1="AM",1,0),0) + IF(AND(A1>=1, A1<=4),IF(B1="PM",1,0),0) + IF(AND(A1=12),IF(B1="PM",1,0),0)

2.) =--OR(AND(A1>=8, B1="AM", A1<>12), AND(OR(A1<=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"))

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.

 =--OR(AND(HOUR>=8,'AM/PM'="AM",HOUR<>12), AND(OR(HOUR<=4,HOUR=12),'AM/PM'="PM"))

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?

Thanks for any help on this

Natrajx
  • 21
  • 1
  • 3

2 Answers2

0

I think the limitation is not you, but Excel.

See here for description of what is possible, as well as this question

Community
  • 1
  • 1
Rusan Kax
  • 1,894
  • 2
  • 13
  • 17
0

I tried your code and indeed I see it's not working. Even with a simple if code it doesn't seem to work. I think it's is explicitly called a calculated field because you are only able to calculate the fields in the Sum/Total/Count etc. column.

Have a look at MS, this function is quite limited. I would try to make another work-around to accomplish your goal.

Microsoft

Riverside
  • 207
  • 1
  • 12