Adding a calculated field to a pivot table is not giving me the results that I think it should.

[OodaLoop]

Hello everyone.

I have a pivot table that has two columns that contain dates.

I am trying to add a column with a OR formula that returns true or false base on whether the first date is equal to the second date OR the first date +1 equals the second date.

The formuala I use is

=OR(FirstDate=SecondDate,FirstDate+1=SecondDate)

But my result looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]12/12/2012[/TD]
[TD]1/4/2013[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9/28/2012[/TD]
[TD]9/28/2013[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

When I create a column manually that isn't part of the Pivot Table it works, but when I add a calculated field for the pivot table I get that.

The field is formatted as General. When I change it to short date I just get 1/0/1900.

Thoughts on what is going on here?

 

[OodaLoop]

Thanks for the move. I read too fast and thought it said Pivot Table questions.

 

[par60056]

Are FirstDate and SecondDate data fields or row fields in the pivot table?

Generally speaking, data fields have some function performed on them. Because of this, calculated fields often do not do what you think they will.

What you probably want to do is add the column "IsCurrent" to the data table you are using to make the pivot table and put your formula in there.

 

[OodaLoop]

It is a row in a Tabular Table.

What do you mean by ad the "IsCurrent" column?

 

[par60056]

You said that this is a pivot table.

Is it just a table and not a pivot table?

Did I misunderstand?


A pivot table is often used to summarize information from a table.

 

[OodaLoop]

Sorry. It is a Tabular Pivot Table. Didn't mean to confuse.

 

[Marcelo Branco]

I can not reproduce your problem (Excel 2007)

The formula below worked for me (Calculated Field)
=OR(Date1=Date2-1,Date1 =Date2 )

Dates as dd/mm/yyyy

[TABLE="width: 314"]
<colgroup><col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4571;" width="125"> <col style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;" width="110"> <col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;" width="104"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <tbody>[TR]
[TD="class: xl65, width: 125"]Name[/TD]
[TD="class: xl65, width: 110"]Date1[/TD]
[TD="class: xl65, width: 104"]Date2[/TD]
[TD="class: xl65, width: 79"]Compare[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10/01/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]14/01/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/01/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/01/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/01/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/01/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]13/01/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]14/01/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Column Compare (calc field) formatted as Number no decimal places

M.

 

[par60056]

OK so you have a table like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]StartDate[/TD]
[TD]EndDate[/TD]
[TD]OtherData[/TD]
[/TR]
[TR]
[TD]12/12/2012[/TD]
[TD]1/4/2013[/TD]
[TD]2,133[/TD]
[/TR]
[TR]
[TD]9/28/2012[/TD]
[TD]9/28/2013[/TD]
[TD]62,123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

what you want is

[TABLE="width: 500"]
<tbody>[TR]
[TD]FirstDate[/TD]
[TD]SecondDate[/TD]
[TD]OtherData[/TD]
[TD]isCurrent[/TD]
[/TR]
[TR]
[TD]12/12/2012[/TD]
[TD]1/4/2013[/TD]
[TD]2,133[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9/28/2012[/TD]
[TD]9/28/2013[/TD]
[TD]62,123[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



under iscurrent, put in the formula:

=if(or([@FirstDate]=[@SecondDate],[@FirstDate]+1=[@SecondDate]),1,0)

You were pretty close originally.