Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
I will have various dates entered in B4 for example which will include
the year, ie dd/mm/yyyy format. I want a formula like the one below which will identify the Spring, Summer, Autumn, Winter each of which is defined as falling between particular dates. I could just expand the formula below to accommodate this. However the problem is I want the formula to operate over many years., eg if B4 is 1/2/2007 I want it to show Spring but obviously this formula cannot do this as it specifies the year. Is there anyway to create it so that it is just looking between 1st February and 31st March in any year, regardless of the year entry in B4. Grateful for any guidance. =IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4) Kind regards Graham Haughs Turriff, Scotland |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
If your definition of spring is 2/1/2007 through 3/31/2007 (using American
date format) then, assuming the dates in question are in column A, =IF(OR(MONTH(A1)=2,MONTH(A1)=3),"Spring","Another season") This will remain true for all subsequent and previous years, if, as above, you define "spring" as being between February 1st and March 31st. You can do a similar formula for other seasons, depending on how you define them. Dave -- Brevity is the soul of wit. "Graham Haughs" wrote: I will have various dates entered in B4 for example which will include the year, ie dd/mm/yyyy format. I want a formula like the one below which will identify the Spring, Summer, Autumn, Winter each of which is defined as falling between particular dates. I could just expand the formula below to accommodate this. However the problem is I want the formula to operate over many years., eg if B4 is 1/2/2007 I want it to show Spring but obviously this formula cannot do this as it specifies the year. Is there anyway to create it so that it is just looking between 1st February and 31st March in any year, regardless of the year entry in B4. Grateful for any guidance. =IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4) Kind regards Graham Haughs Turriff, Scotland |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
I think I mis-read your post. Don's formula is a more accurate one.
-- Brevity is the soul of wit. "Dave F" wrote: If your definition of spring is 2/1/2007 through 3/31/2007 (using American date format) then, assuming the dates in question are in column A, =IF(OR(MONTH(A1)=2,MONTH(A1)=3),"Spring","Another season") This will remain true for all subsequent and previous years, if, as above, you define "spring" as being between February 1st and March 31st. You can do a similar formula for other seasons, depending on how you define them. Dave -- Brevity is the soul of wit. "Graham Haughs" wrote: I will have various dates entered in B4 for example which will include the year, ie dd/mm/yyyy format. I want a formula like the one below which will identify the Spring, Summer, Autumn, Winter each of which is defined as falling between particular dates. I could just expand the formula below to accommodate this. However the problem is I want the formula to operate over many years., eg if B4 is 1/2/2007 I want it to show Spring but obviously this formula cannot do this as it specifies the year. Is there anyway to create it so that it is just looking between 1st February and 31st March in any year, regardless of the year entry in B4. Grateful for any guidance. =IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4) Kind regards Graham Haughs Turriff, Scotland |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
Thanks for that Don.
Graham Don Guillett wrote: =if(and(month(b4)=1,month(b4)<=3) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
If you follow weather conventions:
Dec, Jan, Feb - winter Mar, Apr, May - spring Jun, Jul, Aug - summer Sep, Oct, Nov - fall then: =CHOOSE(MONTH(B4),"winter","winter","spring","spri ng","spring","summer","summer","summer","fall","fa ll","fall","winter") may be of value. You can adjust the list if, for example, you want Feb to be spring. -- Gary's Student "Graham Haughs" wrote: I will have various dates entered in B4 for example which will include the year, ie dd/mm/yyyy format. I want a formula like the one below which will identify the Spring, Summer, Autumn, Winter each of which is defined as falling between particular dates. I could just expand the formula below to accommodate this. However the problem is I want the formula to operate over many years., eg if B4 is 1/2/2007 I want it to show Spring but obviously this formula cannot do this as it specifies the year. Is there anyway to create it so that it is just looking between 1st February and 31st March in any year, regardless of the year entry in B4. Grateful for any guidance. =IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4) Kind regards Graham Haughs Turriff, Scotland |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
This might be a way to assign any date to a season:
Where (in my fake world) the seasons are , Dec-Feb Winter Mar-May Spring Jun-Aug Summer Sep-Nov Autumn The beginning of each season is coded as the MonthNum and the Day. So Autumn begins September first: 901, Winter begins December first: 1201, etc For a date in B4 This formula returns the season: =LOOKUP(--TEXT(N(B4),"mdd"),{0,101,301,601,901,1201},{"no date","Winter","Spring","Summer","Autumn","Winter" }) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Graham Haughs" wrote: I will have various dates entered in B4 for example which will include the year, ie dd/mm/yyyy format. I want a formula like the one below which will identify the Spring, Summer, Autumn, Winter each of which is defined as falling between particular dates. I could just expand the formula below to accommodate this. However the problem is I want the formula to operate over many years., eg if B4 is 1/2/2007 I want it to show Spring but obviously this formula cannot do this as it specifies the year. Is there anyway to create it so that it is just looking between 1st February and 31st March in any year, regardless of the year entry in B4. Grateful for any guidance. =IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4) Kind regards Graham Haughs Turriff, Scotland |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates, Years and Seasons
Nice one, thanks again to all. By the way I think your "Fall" is far
better terminology than our "Autumn" Graham Gary''s Student wrote: If you follow weather conventions: Dec, Jan, Feb - winter Mar, Apr, May - spring Jun, Jul, Aug - summer Sep, Oct, Nov - fall then: =CHOOSE(MONTH(B4),"winter","winter","spring","spri ng","spring","summer","summer","summer","fall","fa ll","fall","winter") may be of value. You can adjust the list if, for example, you want Feb to be spring. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|