If date is in the month of Sept, then "1", otherwise "2"
cell a1 = the date
a1 could equal 9/15/2007, 8/20/2007, 9/26/2007, 5/30/2006, etc. formula please?: if the date in cell a1 falls in the month of september, then "1", otherwise "2" thanks very much! |
If date is in the month of Sept, then "1", otherwise "2"
Try this
=1+(MONTH(A1)<9) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "SteveC" wrote in message ... cell a1 = the date a1 could equal 9/15/2007, 8/20/2007, 9/26/2007, 5/30/2006, etc. formula please?: if the date in cell a1 falls in the month of september, then "1", otherwise "2" thanks very much! |
If date is in the month of Sept, then "1", otherwise "2"
That does help, thanks.
I've modified it to this: =IF(MONTH(N10)=A5,N20,M20) So if the month in cell n10 equals today's month (in cell a5), then use the data in cell n20, otherwise use next month's data which is in cell m20. I will probably have a bunch of nested Ifs where M20 is, becuase I have to reference a few date ranges. Thanks very much Ron! "Ron Coderre" wrote: Try this =1+(MONTH(A1)<9) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "SteveC" wrote in message ... cell a1 = the date a1 could equal 9/15/2007, 8/20/2007, 9/26/2007, 5/30/2006, etc. formula please?: if the date in cell a1 falls in the month of september, then "1", otherwise "2" thanks very much! |
If date is in the month of Sept, then "1", otherwise "2"
I'm glad I could point you in the right direction....thanks for the feedback.
*********** Regards, Ron XL2003, WinXP "SteveC" wrote: That does help, thanks. I've modified it to this: =IF(MONTH(N10)=A5,N20,M20) So if the month in cell n10 equals today's month (in cell a5), then use the data in cell n20, otherwise use next month's data which is in cell m20. I will probably have a bunch of nested Ifs where M20 is, becuase I have to reference a few date ranges. Thanks very much Ron! "Ron Coderre" wrote: Try this =1+(MONTH(A1)<9) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "SteveC" wrote in message ... cell a1 = the date a1 could equal 9/15/2007, 8/20/2007, 9/26/2007, 5/30/2006, etc. formula please?: if the date in cell a1 falls in the month of september, then "1", otherwise "2" thanks very much! |
If date is in the month of Sept, then "1", otherwise "2"
for some reason the month function doesn't seem to work between links of
separate worksheets. I changed the formula to this: =IF(EOMONTH(Monthly!N10,0)=EOMONTH(A5,0),Monthly!N 20,IF(EOMONTH(Monthly!M10,0)=EOMONTH(A5,0),Monthly !M20,"xxx")) and it works now, but if you know of a better way, I'd like to hear. Thanks again. "Ron Coderre" wrote: Try this =1+(MONTH(A1)<9) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "SteveC" wrote in message ... cell a1 = the date a1 could equal 9/15/2007, 8/20/2007, 9/26/2007, 5/30/2006, etc. formula please?: if the date in cell a1 falls in the month of september, then "1", otherwise "2" thanks very much! |
If date is in the month of Sept, then "1", otherwise "2"
The MONTH function doesn't care which sheet it's referring to.
Anyway, perhaps one of these variations: =IF(MONTH(Monthly!N10)=MONTH(Sheet1!A5),Monthly!N2 0,IF(MONTH(Monthly!M10)=MONTH(Sheet1!A5),Monthly!M 20,"xxx")) or =CHOOSE(SUMPRODUCT((MONTH(Monthly!M10:N10)=MONTH(A 5))*{1,2})+1,"xxx",Monthly!M20,Monthly!N20) or... =CHOOSE(SUMPRODUCT((TEXT(Monthly!M10:N10,"yyyymm") =TEXT(A5,"yyyymm"))*{1,2})+1,"xxx",Monthly!M20,Mon thly!N20) Note: in case text wrap impacts the display, there are NO spaces in those formulas. Does that help? *********** Regards, Ron XL2003, WinXP "SteveC" wrote: for some reason the month function doesn't seem to work between links of separate worksheets. I changed the formula to this: =IF(EOMONTH(Monthly!N10,0)=EOMONTH(A5,0),Monthly!N 20,IF(EOMONTH(Monthly!M10,0)=EOMONTH(A5,0),Monthly !M20,"xxx")) and it works now, but if you know of a better way, I'd like to hear. Thanks again. "Ron Coderre" wrote: Try this =1+(MONTH(A1)<9) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "SteveC" wrote in message ... cell a1 = the date a1 could equal 9/15/2007, 8/20/2007, 9/26/2007, 5/30/2006, etc. formula please?: if the date in cell a1 falls in the month of september, then "1", otherwise "2" thanks very much! |
All times are GMT +1. The time now is 08:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com