Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert date to name of month
My spreadsheet:
A B Results: C 1/5/09 =IF(A1="","",MONTH(A1)) 1 ? In Column C, I need to write a formula that says =IF($B1=1,"January"...) but my statement if FALSE, needs to be, IF($B1=2,"February" and so forth all the way through the calendar. The false will be if a new IF formula. I started with this, =IF($B1=1,"January",IF($B1=2,"February",IF($B1=3," March"))) And all the way down the line to December, with the correct # of close parens (12 of them) but I get an error. Do I need to keep typing in the "IF" for a false statement? Help please :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert date to name of month
=if(a1="","",text(date(2009,a1,1),"mmmm"))
Is one way. holly wrote: My spreadsheet: A B Results: C 1/5/09 =IF(A1="","",MONTH(A1)) 1 ? In Column C, I need to write a formula that says =IF($B1=1,"January"...) but my statement if FALSE, needs to be, IF($B1=2,"February" and so forth all the way through the calendar. The false will be if a new IF formula. I started with this, =IF($B1=1,"January",IF($B1=2,"February",IF($B1=3," March"))) And all the way down the line to December, with the correct # of close parens (12 of them) but I get an error. Do I need to keep typing in the "IF" for a false statement? Help please :) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert date to name of month
By the way, I tried the =TEXT($B1,"mmmm") in cell C1 and it returned January
when it should have returned February because I had the date of 2/9 in cell A1, and a 2 in cell B1. "holly" wrote: My spreadsheet: A B Results 1/5/09 =IF(A1="","",MONTH(A1)) 1 In Column C, I need to write a formula that says =IF($B1=1,"January"...) but my statement if FALSE, needs to be, IF($B1=2,"February" and so forth all the way through the calendar. The false will be if a new IF formula. I started with this, =IF($B1=1,"January",IF($B1=2,"February",IF($B1=3," March"))) And all the way down the line to December, with the correct # of close parens (12 of them) but I get an error. Do I need to keep typing in the "IF" for a false statement? Help please :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert date to name of month
If you simply have a 2 in cell b1 you will get January because Excel thinks
that the date is (January 2, 1900). To have the "mmmm" work you need to have a valid date or make sure that your number is between 32 and 59 inclusive. -- Wag more, bark less "holly" wrote: By the way, I tried the =TEXT($B1,"mmmm") in cell C1 and it returned January when it should have returned February because I had the date of 2/9 in cell A1, and a 2 in cell B1. "holly" wrote: My spreadsheet: A B Results 1/5/09 =IF(A1="","",MONTH(A1)) 1 In Column C, I need to write a formula that says =IF($B1=1,"January"...) but my statement if FALSE, needs to be, IF($B1=2,"February" and so forth all the way through the calendar. The false will be if a new IF formula. I started with this, =IF($B1=1,"January",IF($B1=2,"February",IF($B1=3," March"))) And all the way down the line to December, with the correct # of close parens (12 of them) but I get an error. Do I need to keep typing in the "IF" for a false statement? Help please :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert date to name of month*Dave Peterson
THANK YOU!
"Dave Peterson" wrote: =if(a1="","",text(date(2009,a1,1),"mmmm")) Is one way. holly wrote: My spreadsheet: A B Results: C 1/5/09 =IF(A1="","",MONTH(A1)) 1 ? In Column C, I need to write a formula that says =IF($B1=1,"January"...) but my statement if FALSE, needs to be, IF($B1=2,"February" and so forth all the way through the calendar. The false will be if a new IF formula. I started with this, =IF($B1=1,"January",IF($B1=2,"February",IF($B1=3," March"))) And all the way down the line to December, with the correct # of close parens (12 of them) but I get an error. Do I need to keep typing in the "IF" for a false statement? Help please :) -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert date to name of month
Excel sees dates as numbers starting with a base date (January 1, 1900) as a
plain old number (1/1/1900 = 1) that is nicely formatted. So when you look at 1, 2, 3, ... it's like looking at: 1/1/1900, 1/2/1900, 1/3/1900, ... And all of those belong to January of 1900. So =text(a1,"mmmm") will return January for all these. holly wrote: By the way, I tried the =TEXT($B1,"mmmm") in cell C1 and it returned January when it should have returned February because I had the date of 2/9 in cell A1, and a 2 in cell B1. "holly" wrote: My spreadsheet: A B Results 1/5/09 =IF(A1="","",MONTH(A1)) 1 In Column C, I need to write a formula that says =IF($B1=1,"January"...) but my statement if FALSE, needs to be, IF($B1=2,"February" and so forth all the way through the calendar. The false will be if a new IF formula. I started with this, =IF($B1=1,"January",IF($B1=2,"February",IF($B1=3," March"))) And all the way down the line to December, with the correct # of close parens (12 of them) but I get an error. Do I need to keep typing in the "IF" for a false statement? Help please :) -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert date to name of month
But you could get creative and use something like:
=if(a1="","",text(a1*28,"mmmm")) All those values (1*28, 2*28, ..., 12*28) will refer to dates in 1900, but since you're just looking at the month, it doesn't matter. holly wrote: By the way, I tried the =TEXT($B1,"mmmm") in cell C1 and it returned January when it should have returned February because I had the date of 2/9 in cell A1, and a 2 in cell B1. "holly" wrote: My spreadsheet: A B Results 1/5/09 =IF(A1="","",MONTH(A1)) 1 In Column C, I need to write a formula that says =IF($B1=1,"January"...) but my statement if FALSE, needs to be, IF($B1=2,"February" and so forth all the way through the calendar. The false will be if a new IF formula. I started with this, =IF($B1=1,"January",IF($B1=2,"February",IF($B1=3," March"))) And all the way down the line to December, with the correct # of close parens (12 of them) but I get an error. Do I need to keep typing in the "IF" for a false statement? Help please :) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula convert date to month format | Excel Worksheet Functions | |||
Convert date to number representing Month | Excel Discussion (Misc queries) | |||
Convert date field to month in Pivot table | Excel Discussion (Misc queries) | |||
How do I convert a month-date format to day number of the year? | Excel Worksheet Functions | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions |