Correctly Identify Seasons

[iamnu]

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?

 

[T. Valko]

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?

 

[iamnu]

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?

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?

 

[Bernard Liengme]

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

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?

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?

 

[iamnu]

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






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

 

[iamnu]

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?

 

[Bernard Liengme]

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

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?