Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date format
I have a large spreadsheet in Excel. Dates were entered as "August 1, 2001".
When I go to sort the list, Excel sorts by month. I selected all of the date cells, format, and it shows all date cells as Date formated. How can I get Excel to sort by date (by 08/01/2001; not alpha by month)? Or, how can I take the August 1, 2001 and convert into 08/01/2001? Thanks for any help. |
#2
|
|||
|
|||
Date format
Sounds like your dates were entered as character strings/labels, not true
dates. Try entering a formula to calc the difference between a couple of them. If the formula returns #VALUE, they're certainly labels and will need to be converted. Sorry, I don't have a handy formula for that, though others here may. A pretty common problem. I tried sorting a list with a few dates formatted to appear as you describe, but they sort in natural date order. I'm betting you have labels, not dates. "Amy needs help." wrote: I have a large spreadsheet in Excel. Dates were entered as "August 1, 2001". When I go to sort the list, Excel sorts by month. I selected all of the date cells, format, and it shows all date cells as Date formated. How can I get Excel to sort by date (by 08/01/2001; not alpha by month)? Or, how can I take the August 1, 2001 and convert into 08/01/2001? Thanks for any help. |
#3
|
|||
|
|||
Date format
Yes that's true. Does anyone have a formula for this problem?
Thanks. "Jim Sweet" wrote: Sounds like your dates were entered as character strings/labels, not true dates. Try entering a formula to calc the difference between a couple of them. If the formula returns #VALUE, they're certainly labels and will need to be converted. Sorry, I don't have a handy formula for that, though others here may. A pretty common problem. I tried sorting a list with a few dates formatted to appear as you describe, but they sort in natural date order. I'm betting you have labels, not dates. "Amy needs help." wrote: I have a large spreadsheet in Excel. Dates were entered as "August 1, 2001". When I go to sort the list, Excel sorts by month. I selected all of the date cells, format, and it shows all date cells as Date formated. How can I get Excel to sort by date (by 08/01/2001; not alpha by month)? Or, how can I take the August 1, 2001 and convert into 08/01/2001? Thanks for any help. |
#4
|
|||
|
|||
Date format
On Fri, 28 Oct 2005 11:01:03 -0700, Amy needs help.
wrote: Yes that's true. Does anyone have a formula for this problem? Thanks. If they are text and are all in the same format you posted, then =DATEVALUE(A1) will convert. The DATEVALUE function can be finicky, though. It will convert August 1, 2001 but will give an error on: August 1,2001 Post back with your results. If DATEVALUE doesn't work, there are other solutions. --ron |
#5
|
|||
|
|||
Date format
If they were all entered in the format you described, then enter 1 in a dummy
cell and copy it. Select the range of dates you want to change and select paste special and multiply. This will change the text dates to a serial number (8/1/2001 equalls 37104). Finaly format the cells as dates. "Amy needs help." wrote: Yes that's true. Does anyone have a formula for this problem? Thanks. "Jim Sweet" wrote: Sounds like your dates were entered as character strings/labels, not true dates. Try entering a formula to calc the difference between a couple of them. If the formula returns #VALUE, they're certainly labels and will need to be converted. Sorry, I don't have a handy formula for that, though others here may. A pretty common problem. I tried sorting a list with a few dates formatted to appear as you describe, but they sort in natural date order. I'm betting you have labels, not dates. "Amy needs help." wrote: I have a large spreadsheet in Excel. Dates were entered as "August 1, 2001". When I go to sort the list, Excel sorts by month. I selected all of the date cells, format, and it shows all date cells as Date formated. How can I get Excel to sort by date (by 08/01/2001; not alpha by month)? Or, how can I take the August 1, 2001 and convert into 08/01/2001? Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change general format to US date format | Excel Discussion (Misc queries) | |||
Customized Date Format | Excel Worksheet Functions | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
How do I keep the date from changing format in a mail merge? | Excel Discussion (Misc queries) | |||
USING THE DATE FORMAT IN EXCEL | Excel Discussion (Misc queries) |