DATE FORMATTING

ROBERT MARCHETTI · May 1st, 2002, 07:30 AM

When receiving a document with uk dates as dd/mm/yyyy, i change my regional settings to uk( as they are always on US usually), however excel does not recognize these as dates unless I hit F2 then enter all the way down, this is not practical when I have a thousand cells of dates that I wish to sort in year order. What can I do to make sure these are formatted as dates. thanks

Chris Davison · May 1st, 2002, 02:21 PM

could you not just highlight that particular column and format it (special) as yyyy/mm/dd

this will allow you to sort by year, then month then day

_________________
Hope this helps,
Chris

if excel doesn't recognise them initally as dates, does the data / text-to-columns utility help ? just select the column in question and format as a date field

[ This Message was edited by: Chris Davison on 2002-05-01 13:23 ]

zacemmel · May 1st, 2002, 02:25 PM

Format the cells as you wish. Then, enter the number 1 into a cell somewhere. Select that cell, copy it. Select your range of dates, do a paste special and select values and multiply, then click ok.

ROBERT MARCHETTI · May 2nd, 2002, 01:35 AM

thanks, last suggestion worked regarding using 1 in a cell somwhere else, thanks for the help!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 1st, 2002, 07:30 AM	#1
ROBERT MARCHETTI New Member Join Date: Apr 2002 Posts: 6	When receiving a document with uk dates as dd/mm/yyyy, i change my regional settings to uk( as they are always on US usually), however excel does not recognize these as dates unless I hit F2 then enter all the way down, this is not practical when I have a thousand cells of dates that I wish to sort in year order. What can I do to make sure these are formatted as dates. thanks

May 1st, 2002, 02:21 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	could you not just highlight that particular column and format it (special) as yyyy/mm/dd this will allow you to sort by year, then month then day _________________ Hope this helps, Chris if excel doesn't recognise them initally as dates, does the data / text-to-columns utility help ? just select the column in question and format as a date field [ This Message was edited by: Chris Davison on 2002-05-01 13:23 ]

May 1st, 2002, 02:25 PM	#3
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	Format the cells as you wish. Then, enter the number 1 into a cell somewhere. Select that cell, copy it. Select your range of dates, do a paste special and select values and multiply, then click ok.

May 2nd, 2002, 01:35 AM	#4
ROBERT MARCHETTI New Member Join Date: Apr 2002 Posts: 6	thanks, last suggestion worked regarding using 1 in a cell somwhere else, thanks for the help!