Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
I have a worksheet with one column being dates in the format of 3/2, 4/25,
12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
Pete,
When you enter 3/2 or 25/4 despite not entering a year Excel recognises it as a date and add the current year but you wont see it because of your formatting and that's why you sort has gone pear shaped since the start of the new year. If you want to sort by day and month only then you need to create another column to do this called a helper column. Lets say your dates are in column A starting in A1. Put this in B1 and drag down as far as there are data in columnA =DAY(A1)&MONTH(A1) Select both columns and sort on this newly created column and your dates will sort as you want them. You can then hide or delete the helper column but if you frequently add dates then perhaps hidden is the best option. Mike "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
OOPS,
That wont work, you need 2 helper columns Column B formula =day(a1) Column C formula =Month(A1) Now drag these 2 column down and then do a sort first on column C ascending and a secondry sort on column B ascending. Hide both the helper columns. Mike "Mike H" wrote: Pete, When you enter 3/2 or 25/4 despite not entering a year Excel recognises it as a date and add the current year but you wont see it because of your formatting and that's why you sort has gone pear shaped since the start of the new year. If you want to sort by day and month only then you need to create another column to do this called a helper column. Lets say your dates are in column A starting in A1. Put this in B1 and drag down as far as there are data in columnA =DAY(A1)&MONTH(A1) Select both columns and sort on this newly created column and your dates will sort as you want them. You can then hide or delete the helper column but if you frequently add dates then perhaps hidden is the best option. Mike "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
Or use a different formula in a single helper column:
=text(a1,"mmdd") or =(month(a1)*100)+day(a1) Mike H wrote: OOPS, That wont work, you need 2 helper columns Column B formula =day(a1) Column C formula =Month(A1) Now drag these 2 column down and then do a sort first on column C ascending and a secondry sort on column B ascending. Hide both the helper columns. Mike "Mike H" wrote: Pete, When you enter 3/2 or 25/4 despite not entering a year Excel recognises it as a date and add the current year but you wont see it because of your formatting and that's why you sort has gone pear shaped since the start of the new year. If you want to sort by day and month only then you need to create another column to do this called a helper column. Lets say your dates are in column A starting in A1. Put this in B1 and drag down as far as there are data in columnA =DAY(A1)&MONTH(A1) Select both columns and sort on this newly created column and your dates will sort as you want them. You can then hide or delete the helper column but if you frequently add dates then perhaps hidden is the best option. Mike "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
Re the Helper Column(s). Am I just to copy everything to another column (B)
and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
What happend when you used the =text() formula?
What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow
but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
This helper column of formulas doesn't replace the original data. You can
either use an unused column or insert an empty column (usually adjacent near the original data). So if you put the dates in column A, then you could use column B to hold the formula: =text(a2,"mmdd") If there is no autofill button/grab handle, try toggling this setting: Tools|Options|Edit tab|Check the "Allow cell drag and drop" box. (xl2003 menus) pete5440 wrote: Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
Dave I appreciate the tips but this isn't happening. I'm using Excel 2007 and
never had issues like this in previous versions. Somehow I have managed to changed my first date from 1/1 to1-Jan in playing with this and I can't change it back. I have added a 'blank' helper column (C) and if enter the formula, =text(b3,"mmdd") and pull this down it will change my dates from xx/xx to xxxx without the "/" separating the month and day. I need the xx/xx format. "Dave Peterson" wrote: This helper column of formulas doesn't replace the original data. You can either use an unused column or insert an empty column (usually adjacent near the original data). So if you put the dates in column A, then you could use column B to hold the formula: =text(a2,"mmdd") If there is no autofill button/grab handle, try toggling this setting: Tools|Options|Edit tab|Check the "Allow cell drag and drop" box. (xl2003 menus) pete5440 wrote: Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
This helper column is only temporary -- it's only there until you finish the
sort. You can delete it as soon as you sort all the data by this column. If you do it lots of times, you may want to keep the column, but hide it so that it's not confusing to the user (you???). If you select the cell with the date and see the real date in the formula bar, then reformat the cell to display the date in the way you like. pete5440 wrote: Dave I appreciate the tips but this isn't happening. I'm using Excel 2007 and never had issues like this in previous versions. Somehow I have managed to changed my first date from 1/1 to1-Jan in playing with this and I can't change it back. I have added a 'blank' helper column (C) and if enter the formula, =text(b3,"mmdd") and pull this down it will change my dates from xx/xx to xxxx without the "/" separating the month and day. I need the xx/xx format. "Dave Peterson" wrote: This helper column of formulas doesn't replace the original data. You can either use an unused column or insert an empty column (usually adjacent near the original data). So if you put the dates in column A, then you could use column B to hold the formula: =text(a2,"mmdd") If there is no autofill button/grab handle, try toggling this setting: Tools|Options|Edit tab|Check the "Allow cell drag and drop" box. (xl2003 menus) pete5440 wrote: Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
If, instead of mmdd format text, you want to see mm/dd format text, just
change your =text(b3,"mmdd") formula to =text(b3,"mm/dd") -- David Biddulph "pete5440" wrote in message ... Dave I appreciate the tips but this isn't happening. I'm using Excel 2007 and never had issues like this in previous versions. Somehow I have managed to changed my first date from 1/1 to1-Jan in playing with this and I can't change it back. I have added a 'blank' helper column (C) and if enter the formula, =text(b3,"mmdd") and pull this down it will change my dates from xx/xx to xxxx without the "/" separating the month and day. I need the xx/xx format. "Dave Peterson" wrote: This helper column of formulas doesn't replace the original data. You can either use an unused column or insert an empty column (usually adjacent near the original data). So if you put the dates in column A, then you could use column B to hold the formula: =text(a2,"mmdd") If there is no autofill button/grab handle, try toggling this setting: Tools|Options|Edit tab|Check the "Allow cell drag and drop" box. (xl2003 menus) pete5440 wrote: Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
Thanks Dave and David. David I tried the xx/xx in the formula a half dozens
times and kept getting error message about invalid characters "/". Finally, it does work. One last and hopefully final question though. I will keep adding dates to this list on an ongoing basis. So you are saying I will have to keep the Helper Column to do my sort? Wouldn't it be somehow easier to just have the formula 'work' (by that I mean do not associate a year with the mm/dd) every time I entered the dates? "David Biddulph" wrote: If, instead of mmdd format text, you want to see mm/dd format text, just change your =text(b3,"mmdd") formula to =text(b3,"mm/dd") -- David Biddulph "pete5440" wrote in message ... Dave I appreciate the tips but this isn't happening. I'm using Excel 2007 and never had issues like this in previous versions. Somehow I have managed to changed my first date from 1/1 to1-Jan in playing with this and I can't change it back. I have added a 'blank' helper column (C) and if enter the formula, =text(b3,"mmdd") and pull this down it will change my dates from xx/xx to xxxx without the "/" separating the month and day. I need the xx/xx format. "Dave Peterson" wrote: This helper column of formulas doesn't replace the original data. You can either use an unused column or insert an empty column (usually adjacent near the original data). So if you put the dates in column A, then you could use column B to hold the formula: =text(a2,"mmdd") If there is no autofill button/grab handle, try toggling this setting: Tools|Options|Edit tab|Check the "Allow cell drag and drop" box. (xl2003 menus) pete5440 wrote: Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
If the year doesn't matter, then you could always used 2008 (so Feb 29 is
valid). Just make sure you include the year when you type the date. pete5440 wrote: Thanks Dave and David. David I tried the xx/xx in the formula a half dozens times and kept getting error message about invalid characters "/". Finally, it does work. One last and hopefully final question though. I will keep adding dates to this list on an ongoing basis. So you are saying I will have to keep the Helper Column to do my sort? Wouldn't it be somehow easier to just have the formula 'work' (by that I mean do not associate a year with the mm/dd) every time I entered the dates? "David Biddulph" wrote: If, instead of mmdd format text, you want to see mm/dd format text, just change your =text(b3,"mmdd") formula to =text(b3,"mm/dd") -- David Biddulph "pete5440" wrote in message ... Dave I appreciate the tips but this isn't happening. I'm using Excel 2007 and never had issues like this in previous versions. Somehow I have managed to changed my first date from 1/1 to1-Jan in playing with this and I can't change it back. I have added a 'blank' helper column (C) and if enter the formula, =text(b3,"mmdd") and pull this down it will change my dates from xx/xx to xxxx without the "/" separating the month and day. I need the xx/xx format. "Dave Peterson" wrote: This helper column of formulas doesn't replace the original data. You can either use an unused column or insert an empty column (usually adjacent near the original data). So if you put the dates in column A, then you could use column B to hold the formula: =text(a2,"mmdd") If there is no autofill button/grab handle, try toggling this setting: Tools|Options|Edit tab|Check the "Allow cell drag and drop" box. (xl2003 menus) pete5440 wrote: Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Dates
If you don't want the number treated as a full date, put it in as text.
-- David Biddulph "pete5440" wrote in message ... Thanks Dave and David. David I tried the xx/xx in the formula a half dozens times and kept getting error message about invalid characters "/". Finally, it does work. One last and hopefully final question though. I will keep adding dates to this list on an ongoing basis. So you are saying I will have to keep the Helper Column to do my sort? Wouldn't it be somehow easier to just have the formula 'work' (by that I mean do not associate a year with the mm/dd) every time I entered the dates? "David Biddulph" wrote: If, instead of mmdd format text, you want to see mm/dd format text, just change your =text(b3,"mmdd") formula to =text(b3,"mm/dd") -- David Biddulph "pete5440" wrote in message ... Dave I appreciate the tips but this isn't happening. I'm using Excel 2007 and never had issues like this in previous versions. Somehow I have managed to changed my first date from 1/1 to1-Jan in playing with this and I can't change it back. I have added a 'blank' helper column (C) and if enter the formula, =text(b3,"mmdd") and pull this down it will change my dates from xx/xx to xxxx without the "/" separating the month and day. I need the xx/xx format. "Dave Peterson" wrote: This helper column of formulas doesn't replace the original data. You can either use an unused column or insert an empty column (usually adjacent near the original data). So if you put the dates in column A, then you could use column B to hold the formula: =text(a2,"mmdd") If there is no autofill button/grab handle, try toggling this setting: Tools|Options|Edit tab|Check the "Allow cell drag and drop" box. (xl2003 menus) pete5440 wrote: Here's the formula: =TEXT(B2,"mmdd"). The b2 cell is highlighted in yellow but no 'grab handle' to pull it down the column. Dates are changed from 1/1, 1/9 etc. to 1/0 with this formula. I'm also getting a "circular warning" at times and when I click on circular references it shows $B$2. I'm really stumped and never seemed to have problems in prior versions of Excel. "Dave Peterson" wrote: What happend when you used the =text() formula? What was in B2? pete5440 wrote: Re the Helper Column(s). Am I just to copy everything to another column (B) and set up a formula there? Mike, with the 2-column approach does this mean I'll have three columns to handle my dates and I'll need to use all three to sort (sorry, I'm confused)? In trying to let the help function of excel guide me, and posting this same inquiry in the excel worksheet thread, the question about "text" came up. I'm looking for the easiest (isn't everyone) way to do this. I tried the =text(b2,"mmdd") but it didn't work. "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day. This is Excel 2007. How do I fix? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Dates | Excel Worksheet Functions | |||
sort dates | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
sort dates | Excel Worksheet Functions |