Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change 12/5/2007 to 12/1/2005
I need a formula to add 2000 to the day, or the middle number between
the forward slashes, and make it the year and then make the day 1 or 15 or any number. Can anyone help with that. One problem is sometimes it's one digit and other times it's two. Ultimately, I'd like to graph the worksheet with the date (month, year) as the X axis. Alan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
change 12/5/2007 to 12/1/2005
=DATE(2000+DAY(A1),MONTH(A1),1)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan Calan" wrote in message ... I need a formula to add 2000 to the day, or the middle number between the forward slashes, and make it the year and then make the day 1 or 15 or any number. Can anyone help with that. One problem is sometimes it's one digit and other times it's two. Ultimately, I'd like to graph the worksheet with the date (month, year) as the X axis. Alan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
change 12/5/2007 to 12/1/2005
Bob, thanks so much, it worked perfectly.
However, some of the dates in column E were Ok, like those for the 2000 and below. I have no idea why that is true. Those I don't want to change. And for most of the others there were no sales in column F, so there should be no date. =DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is there any way we can add those tests? I tried myself and got FALSE in cells where I wanted blanks. On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips" wrote: =DATE(2000+DAY(A1),MONTH(A1),1) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
change 12/5/2007 to 12/1/2005
Sticking to Bob's original cell references, try this:
=IF(F1=0,"",IF(YEAR(A1)<2000,A1,DATE(2000+DAY(A1), MONTH(A1),1))) You appear to have your dates in column E, so make the necessary changes to A1 (4 times). Hope this helps. Pete On Oct 30, 11:14 pm, Alan Calan wrote: Bob, thanks so much, it worked perfectly. However, some of the dates in column E were Ok, like those for the 2000 and below. I have no idea why that is true. Those I don't want to change. And for most of the others there were no sales in column F, so there should be no date. =DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is there any way we can add those tests? I tried myself and got FALSE in cells where I wanted blanks. On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips" wrote: =DATE(2000+DAY(A1),MONTH(A1),1)- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
change 12/5/2007 to 12/1/2005
Pete and Bob,
Thanks so much and I learned a few things too. I had the logic almost correct after Bob's formula, I didn't know you could pull days and months and years out of the date format, but also I don't think I knew "" puts nothing in a cell. I thought I could just skip it or comma passed it. That, plus some of the cells had garbage in them made it impossible for me to see the forest through the trees. I don't even know how it happened but some cells on the text to columns looked like this: 01 $240,000 I guess it just sucks in everything it sees sequentially. So, thanks again for the help. Alan On Tue, 30 Oct 2007 16:39:52 -0700, Pete_UK wrote: Sticking to Bob's original cell references, try this: =IF(F1=0,"",IF(YEAR(A1)<2000,A1,DATE(2000+DAY(A1) ,MONTH(A1),1))) You appear to have your dates in column E, so make the necessary changes to A1 (4 times). Hope this helps. Pete On Oct 30, 11:14 pm, Alan Calan wrote: Bob, thanks so much, it worked perfectly. However, some of the dates in column E were Ok, like those for the 2000 and below. I have no idea why that is true. Those I don't want to change. And for most of the others there were no sales in column F, so there should be no date. =DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is there any way we can add those tests? I tried myself and got FALSE in cells where I wanted blanks. On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips" wrote: =DATE(2000+DAY(A1),MONTH(A1),1)- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
change 12/5/2007 to 12/1/2005
You're welcome, Alan - thanks for feeding back.
Pete On Nov 3, 2:30 pm, Alan Calan wrote: Pete and Bob, Thanks so much and I learned a few things too. I had the logic almost correct after Bob's formula, I didn't know you could pull days and months and years out of the date format, but also I don't think I knew "" puts nothing in a cell. I thought I could just skip it or comma passed it. That, plus some of the cells had garbage in them made it impossible for me to see the forest through the trees. I don't even know how it happened but some cells on the text to columns looked like this: 01 $240,000 I guess it just sucks in everything it sees sequentially. So, thanks again for the help. Alan On Tue, 30 Oct 2007 16:39:52 -0700, Pete_UK wrote: Sticking to Bob's original cell references, try this: =IF(F1=0,"",IF(YEAR(A1)<2000,A1,DATE(2000+DAY(A1) ,MONTH(A1),1))) You appear to have your dates in column E, so make the necessary changes to A1 (4 times). Hope this helps. Pete On Oct 30, 11:14 pm, Alan Calan wrote: Bob, thanks so much, it worked perfectly. However, some of the dates in column E were Ok, like those for the 2000 and below. I have no idea why that is true. Those I don't want to change. And for most of the others there were no sales in column F, so there should be no date. =DATE(2000+DAY(E343),MONTH(E343),1) Here is how it looks now. Is there any way we can add those tests? I tried myself and got FALSE in cells where I wanted blanks. On Mon, 29 Oct 2007 01:58:59 -0000, "Bob Phillips" wrote: =DATE(2000+DAY(A1),MONTH(A1),1)- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I update the 2005 attendance calendar to 2007? | Excel Worksheet Functions | |||
Excel 2007 problems with AS 2005 calculated members | Excel Discussion (Misc queries) | |||
I have a date 51231 (2005,dec,31) -I want to change to 2005/12/31 | Excel Worksheet Functions | |||
HOW DO I CHANGE ABSENCE TEMPLATE 2005, TO 2006 | Excel Worksheet Functions |