I have a spread sheet that has daily weather information.

I have a column "Seasons" that specifies if the date is "Summer",
"Fall", "Winter" or "Spring".

Since Winter begins on ~December 21 and ends on ~March 21, my Pivot
Table is showing the wrong information for "Winter", because it is not
using December values from the previous year.

Can someone give me a suggestion as to how to "fix" my spreadsheet to
properly display the "Seasons" information in a pivot table?

Are you using constant dates for the seasons? The seasonal dates aren't the
same every year.

Spring can be either March 20 or 21
Summer can be either June 20 or 21
Fall can be either September 22 or 23
Winter can be either December 21 or 22

Are your dates all within the same year?

--
Biff
Microsoft Excel MVP


"iamnu" <iamnubw@gmail.com> wrote in message
news:d8d7c9a7-07e4-48c7-b24c-e6c45c5876ad@k36g2000pri.googlegroups.com...
>I have a spread sheet that has daily weather information.
>
> I have a column "Seasons" that specifies if the date is "Summer",
> "Fall", "Winter" or "Spring".
>
> Since Winter begins on ~December 21 and ends on ~March 21, my Pivot
> Table is showing the wrong information for "Winter", because it is not
> using December values from the previous year.
>
> Can someone give me a suggestion as to how to "fix" my spreadsheet to
> properly display the "Seasons" information in a pivot table?

On Jul 20, 9:39*pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Are you using constant dates for the seasons? The seasonal dates aren't the
> same every year.
>
> Spring can be either March 20 or 21
> Summer can be either June 20 or 21
> Fall can be either September 22 or 23
> Winter can be either December 21 or 22
>
> Are your dates all within the same year?
>
> --
> Biff
> Microsoft Excel MVP
>
> "iamnu" <iamn...@gmail.com> wrote in message
>
> news:d8d7c9a7-07e4-48c7-b24c-e6c45c5876ad@k36g2000pri.googlegroups.com...
>
> >I have a spread sheet that has daily weather information.
>
> > I have a column "Seasons" that specifies if the date is "Summer",
> > "Fall", "Winter" or "Spring".
>
> > Since Winter begins on ~December 21 and ends on ~March 21, my Pivot
> > Table is showing the wrong information for "Winter", because it is not
> > using December values from the previous year.
>
> > Can someone give me a suggestion as to how to "fix" my spreadsheet to
> > properly display the "Seasons" information in a pivot table?

I thought it would be sufficient to indicate the dates were
approximate. That is not the issue.
The issue is displaying the dates in the pivot table, which would
require displaying dates from ~December 21 to December 31 of the
previous year, and January 1 to ~March 21 of the subsequent year.
How is this done?

I would use a 'helper' column.
On Sheet2 I have in A1:A5 the numbers: 1,80,172,266 and 356
In B1:B5, I have text: Winter, Spring, Summer, Fall, Winter
On Sheet1 my dates for the pivot table are in column A
In my helper column I have:
=VLOOKUP(DATE(1900,MONTH(A1),DAY(A1)),Sheet2!$A$1: $B$5,2)
This gives me the season
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"iamnu" <iamnubw@gmail.com> wrote in message
news:361baa2c-fea3-495a-b5a7-a6ee3eb9f201@u6g2000prc.googlegroups.com...
On Jul 20, 9:39 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Are you using constant dates for the seasons? The seasonal dates aren't
> the
> same every year.
>
> Spring can be either March 20 or 21
> Summer can be either June 20 or 21
> Fall can be either September 22 or 23
> Winter can be either December 21 or 22
>
> Are your dates all within the same year?
>
> --
> Biff
> Microsoft Excel MVP
>
> "iamnu" <iamn...@gmail.com> wrote in message
>
> news:d8d7c9a7-07e4-48c7-b24c-e6c45c5876ad@k36g2000pri.googlegroups.com...
>
> >I have a spread sheet that has daily weather information.
>
> > I have a column "Seasons" that specifies if the date is "Summer",
> > "Fall", "Winter" or "Spring".
>
> > Since Winter begins on ~December 21 and ends on ~March 21, my Pivot
> > Table is showing the wrong information for "Winter", because it is not
> > using December values from the previous year.
>
> > Can someone give me a suggestion as to how to "fix" my spreadsheet to
> > properly display the "Seasons" information in a pivot table?

I thought it would be sufficient to indicate the dates were
approximate. That is not the issue.
The issue is displaying the dates in the pivot table, which would
require displaying dates from ~December 21 to December 31 of the
previous year, and January 1 to ~March 21 of the subsequent year.
How is this done?

On Jul 21, 6:44*am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> I would use a 'helper' column.
> On Sheet2 I have in A1:A5 the numbers: 1,80,172,266 and 356
> In B1:B5, I have text: Winter, Spring, Summer, Fall, Winter
> On Sheet1 my dates for the pivot table are in column A
> In my helper column I have:
> =VLOOKUP(DATE(1900,MONTH(A1),DAY(A1)),Sheet2!$A$1: $B$5,2)
> This gives me the season
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "iamnu" <iamn...@gmail.com> wrote in message
>
> news:361baa2c-fea3-495a-b5a7-a6ee3eb9f201@u6g2000prc.googlegroups.com...
> On Jul 20, 9:39 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>
>
>
> > Are you using constant dates for the seasons? The seasonal dates aren't
> > the
> > same every year.
>
> > Spring can be either March 20 or 21
> > Summer can be either June 20 or 21
> > Fall can be either September 22 or 23
> > Winter can be either December 21 or 22
>
> > Are your dates all within the same year?
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "iamnu" <iamn...@gmail.com> wrote in message
>
> >news:d8d7c9a7-07e4-48c7-b24c-e6c45c5876ad@k36g2000pri.googlegroups.com....
>
> > >I have a spread sheet that has daily weather information.
>
> > > I have a column "Seasons" that specifies if the date is "Summer",
> > > "Fall", "Winter" or "Spring".
>
> > > Since Winter begins on ~December 21 and ends on ~March 21, my Pivot
> > > Table is showing the wrong information for "Winter", because it is not
> > > using December values from the previous year.
>
> > > Can someone give me a suggestion as to how to "fix" my spreadsheet to
> > > properly display the "Seasons" information in a pivot table?
>
> I thought it would be sufficient to indicate the dates were
> approximate. *That is not the issue.
> The issue is displaying the dates in the pivot table, which would
> require displaying dates from ~December 21 to December 31 of the
> previous year, and January 1 to ~March 21 of the subsequent year.
> How is this done?

Excellent, Mr. Liengme!
I understand your suggestion.
Thanks very much.
Bernie

On Jul 21, 6:57*am, iamnu <iamn...@gmail.com> wrote:
> On Jul 21, 6:44*am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
> wrote:
>
>
>
> > I would use a 'helper' column.
> > On Sheet2 I have in A1:A5 the numbers: 1,80,172,266 and 356
> > In B1:B5, I have text: Winter, Spring, Summer, Fall, Winter
> > On Sheet1 my dates for the pivot table are in column A
> > In my helper column I have:
> > =VLOOKUP(DATE(1900,MONTH(A1),DAY(A1)),Sheet2!$A$1: $B$5,2)
> > This gives me the season
> > best wishes
> > --
> > Bernard V Liengme
> > Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> > remove caps from email
>
> > "iamnu" <iamn...@gmail.com> wrote in message
>
> >news:361baa2c-fea3-495a-b5a7-a6ee3eb9f201@u6g2000prc.googlegroups.com...
> > On Jul 20, 9:39 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>
> > > Are you using constant dates for the seasons? The seasonal dates aren't
> > > the
> > > same every year.
>
> > > Spring can be either March 20 or 21
> > > Summer can be either June 20 or 21
> > > Fall can be either September 22 or 23
> > > Winter can be either December 21 or 22
>
> > > Are your dates all within the same year?
>
> > > --
> > > Biff
> > > Microsoft Excel MVP
>
> > > "iamnu" <iamn...@gmail.com> wrote in message
>
> > >news:d8d7c9a7-07e4-48c7-b24c-e6c45c5876ad@k36g2000pri.googlegroups.com....
>
> > > >I have a spread sheet that has daily weather information.
>
> > > > I have a column "Seasons" that specifies if the date is "Summer",
> > > > "Fall", "Winter" or "Spring".
>
> > > > Since Winter begins on ~December 21 and ends on ~March 21, my Pivot
> > > > Table is showing the wrong information for "Winter", because it is not
> > > > using December values from the previous year.
>
> > > > Can someone give me a suggestion as to how to "fix" my spreadsheet to
> > > > properly display the "Seasons" information in a pivot table?
>
> > I thought it would be sufficient to indicate the dates were
> > approximate. *That is not the issue.
> > The issue is displaying the dates in the pivot table, which would
> > require displaying dates from ~December 21 to December 31 of the
> > previous year, and January 1 to ~March 21 of the subsequent year.
> > How is this done?
>
> Excellent, Mr. Liengme!
> I understand your suggestion.
> Thanks very much.
> Bernie

Well, I spoke too soon. Your suggestion does put the value Winter in
the column, but when looking at the Pivot Table, it still picks up the
values (Temperature for example) for a single year, not overlapping
years as is the case for Winter.
Do you have another suggestion?

Not without seen the worksheet!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"iamnu" <iamnubw@gmail.com> wrote in message
news:676b6599-f524-48ae-8a91-f931927e3679@l64g2000hse.googlegroups.com...
On Jul 21, 6:57 am, iamnu <iamn...@gmail.com> wrote:
> On Jul 21, 6:44 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
> wrote:
>
>
>
> > I would use a 'helper' column.
> > On Sheet2 I have in A1:A5 the numbers: 1,80,172,266 and 356
> > In B1:B5, I have text: Winter, Spring, Summer, Fall, Winter
> > On Sheet1 my dates for the pivot table are in column A
> > In my helper column I have:
> > =VLOOKUP(DATE(1900,MONTH(A1),DAY(A1)),Sheet2!$A$1: $B$5,2)
> > This gives me the season
> > best wishes
> > --
> > Bernard V Liengme
> > Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> > remove caps from email
>
> > "iamnu" <iamn...@gmail.com> wrote in message
>
> >news:361baa2c-fea3-495a-b5a7-a6ee3eb9f201@u6g2000prc.googlegroups.com...
> > On Jul 20, 9:39 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>
> > > Are you using constant dates for the seasons? The seasonal dates
> > > aren't
> > > the
> > > same every year.
>
> > > Spring can be either March 20 or 21
> > > Summer can be either June 20 or 21
> > > Fall can be either September 22 or 23
> > > Winter can be either December 21 or 22
>
> > > Are your dates all within the same year?
>
> > > --
> > > Biff
> > > Microsoft Excel MVP
>
> > > "iamnu" <iamn...@gmail.com> wrote in message
>
> > >news:d8d7c9a7-07e4-48c7-b24c-e6c45c5876ad@k36g2000pri.googlegroups.com...
>
> > > >I have a spread sheet that has daily weather information.
>
> > > > I have a column "Seasons" that specifies if the date is "Summer",
> > > > "Fall", "Winter" or "Spring".
>
> > > > Since Winter begins on ~December 21 and ends on ~March 21, my Pivot
> > > > Table is showing the wrong information for "Winter", because it is
> > > > not
> > > > using December values from the previous year.
>
> > > > Can someone give me a suggestion as to how to "fix" my spreadsheet
> > > > to
> > > > properly display the "Seasons" information in a pivot table?
>
> > I thought it would be sufficient to indicate the dates were
> > approximate. That is not the issue.
> > The issue is displaying the dates in the pivot table, which would
> > require displaying dates from ~December 21 to December 31 of the
> > previous year, and January 1 to ~March 21 of the subsequent year.
> > How is this done?
>
> Excellent, Mr. Liengme!
> I understand your suggestion.
> Thanks very much.
> Bernie

Well, I spoke too soon. Your suggestion does put the value Winter in
the column, but when looking at the Pivot Table, it still picks up the
values (Temperature for example) for a single year, not overlapping
years as is the case for Winter.
Do you have another suggestion?