Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
Hi I am try to set it up on a risk register when to review their actions at a
certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December",IF(J17="February","Mar ch",IF(AND(J17="March",J17="April"),"May",IF(J17=" MAY","June",IF(AND(J17="June",J17="July"),"August" ,IF(J17="August","September",IF(AND(J17="September ",J17="October"),"November"))))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
Hi
One way, enter the following in B1 and copy down =IF(A1="","", CHOOSE(MONTH(A1),"Feb","Mar","May","May", "Jun","Aug","Aug","Sep","Nov","Nov","Dec","Feb ")) Obviously expand names of months to full names if required -- Regards Roger Govier "Pasty" wrote in message ... Hi I am try to set it up on a risk register when to review their actions at a certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December",IF(J17="February","Mar ch",IF(AND(J17="March",J17="April"),"May",IF(J17=" MAY","June",IF(AND(J17="June",J17="July"),"August" ,IF(J17="August","September",IF(AND(J17="September ",J17="October"),"November"))))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
Your ANDs should be ORs.
Then you might get somewhere. Try changing them all and I think it works for you :-) -- Allllen "Pasty" wrote: Hi I am try to set it up on a risk register when to review their actions at a certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December",IF(J17="February","Mar ch",IF(AND(J17="March",J17="April"),"May",IF(J17=" MAY","June",IF(AND(J17="June",J17="July"),"August" ,IF(J17="August","September",IF(AND(J17="September ",J17="October"),"November"))))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
Hi,
Thank you for taking the time to answer but unfortunately this gives me a #value error. "Roger Govier" wrote: Hi One way, enter the following in B1 and copy down =IF(A1="","", CHOOSE(MONTH(A1),"Feb","Mar","May","May", "Jun","Aug","Aug","Sep","Nov","Nov","Dec","Feb ")) Obviously expand names of months to full names if required -- Regards Roger Govier "Pasty" wrote in message ... Hi I am try to set it up on a risk register when to review their actions at a certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December",IF(J17="February","Mar ch",IF(AND(J17="March",J17="April"),"May",IF(J17=" MAY","June",IF(AND(J17="June",J17="July"),"August" ,IF(J17="August","September",IF(AND(J17="September ",J17="October"),"November"))))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
Hi,
Thank you for taking the time to answer this but this gives me a #value error. "Allllen" wrote: Your ANDs should be ORs. Then you might get somewhere. Try changing them all and I think it works for you :-) -- Allllen "Pasty" wrote: Hi I am try to set it up on a risk register when to review their actions at a certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December",IF(J17="February","Mar ch",IF(AND(J17="March",J17="April"),"May",IF(J17=" MAY","June",IF(AND(J17="June",J17="July"),"August" ,IF(J17="August","September",IF(AND(J17="September ",J17="October"),"November"))))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
=TEXT(DATEVALUE("01-"&J17&"-1900")+((MOD(MONTH(DATEVALUE("01-"&J17&"-1900"))
,3)=0)+1)*31,"mmmm") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pasty" wrote in message ... Hi I am try to set it up on a risk register when to review their actions at a certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December ",IF(J17="February","March",IF(AND(J17="March",J17 ="April"),"May",IF(J17="MA Y","June",IF(AND(J17="June",J17="July"),"August",I F(J17="August","September" ,IF(AND(J17="September",J17="October"),"November") )))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
Yep that's answered it, thank you.
"Bob Phillips" wrote: =TEXT(DATEVALUE("01-"&J17&"-1900")+((MOD(MONTH(DATEVALUE("01-"&J17&"-1900")) ,3)=0)+1)*31,"mmmm") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pasty" wrote in message ... Hi I am try to set it up on a risk register when to review their actions at a certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December ",IF(J17="February","March",IF(AND(J17="March",J17 ="April"),"May",IF(J17="MA Y","June",IF(AND(J17="June",J17="July"),"August",I F(J17="August","September" ,IF(AND(J17="September",J17="October"),"November") )))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Months of review
Hi
Didn't notice you were using J17 as the input cell, I used A1. Change reference to J17 and it should work, works fine for me. I also just noticed you have Text months entered in cell J17 as "December" etc. My solution would only work if you had a true Excel Date in J17 like 01/12/2006 (adjusted for your regional settings) and the cell was formatted as FormatCellsNumberCustom mmmm which would cause the cell to display as December, even though it held a true date. For a text entry in J17, Bob has given you an excellent solution. -- Regards Roger Govier "Pasty" wrote in message ... Hi, Thank you for taking the time to answer but unfortunately this gives me a #value error. "Roger Govier" wrote: Hi One way, enter the following in B1 and copy down =IF(A1="","", CHOOSE(MONTH(A1),"Feb","Mar","May","May", "Jun","Aug","Aug","Sep","Nov","Nov","Dec","Feb ")) Obviously expand names of months to full names if required -- Regards Roger Govier "Pasty" wrote in message ... Hi I am try to set it up on a risk register when to review their actions at a certain date after the actions have been set, initially I was under the impression it was always the month after but have since found out it is more complicated than this: Action Date Review Date November December December or January February February March March or April May May June June or July August August September September or October November I have been messing around with an IF function along the lines of =IF(AND(J17="December",J17="January"),"February",I F(J17="November","December",IF(J17="February","Mar ch",IF(AND(J17="March",J17="April"),"May",IF(J17=" MAY","June",IF(AND(J17="June",J17="July"),"August" ,IF(J17="August","September",IF(AND(J17="September ",J17="October"),"November"))))))) But this isn't working so I was wondering if there was a simpler way of doing it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My discovery on adding months and days to a date! | Excel Worksheet Functions | |||
Convert 2 digit month to 4 digit years and months | Excel Worksheet Functions | |||
CAn any one solve this problem of days months and years.its urgent plzzzzzzzzzzzz | Excel Discussion (Misc queries) | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions |