Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Dates
Hi there,
I have inherited a worksheet whereby dates have been recorded in the following way: 311007 for 31st October 2007 11107 for 1st November 2007 etc. I now need to convert these cells into a date format so I can sort the data and perform calculations. I'd be grateful for any suggestions on how I can do this. Paul |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Dates
Can you post a few more examples, showing what happens when you have
dates for September or earlier (single-digit month). Do you get a leading zero in these cases, like: 10907 for 1st September 2007, 100907 for 10th September 2007? If you don't, then couldn't your second example be interpreted as 11th January 2007? Pete On Nov 1, 9:10 pm, Trazza_UK wrote: Hi there, I have inherited a worksheet whereby dates have been recorded in the following way: 311007 for 31st October 2007 11107 for 1st November 2007 etc. I now need to convert these cells into a date format so I can sort the data and perform calculations. I'd be grateful for any suggestions on how I can do this. Paul |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Dates
This is trickier than it should be since the dates aren't always the same
length. Note the leading 0 is missing for the Nov 1 date. If you want to enter dates this way, you should, before entering the data, format the column as text and always enter the leading 0. Else, how would you distinguish 1 Nov from 11 Jan? But assuming these are numbers and Excel has thrown away the leading 0, =DATE(2000+MOD(A1,100),MOD(INT(A1/100),100),INT(A1/10000)) If these are text strings, it also works since Excel helpfully converts the text to numbers on-the-fly. On Thu, 01 Nov 2007 14:10:24 -0700, Trazza_UK wrote: Hi there, I have inherited a worksheet whereby dates have been recorded in the following way: 311007 for 31st October 2007 11107 for 1st November 2007 etc. I now need to convert these cells into a date format so I can sort the data and perform calculations. I'd be grateful for any suggestions on how I can do this. Paul |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Dates
Paul
Here is the problem. 311007 is 31 Oct 07. That is easy to figure out because the month and the day are both 2 digits and neither can be 3 or more digits. But look at 11107. The year is 07. That's easy. But the date could be 1 Nov or 11 Jan. Do you know something about this data that says this MUST be 1 Nov? If you can furnish information to eliminate the ambiguity I just described, then Excel can do what you want. Just imagine you have to instruct someone on how to do this manually. What would you tell him? HTH Otto "Trazza_UK" wrote in message ups.com... Hi there, I have inherited a worksheet whereby dates have been recorded in the following way: 311007 for 31st October 2007 11107 for 1st November 2007 etc. I now need to convert these cells into a date format so I can sort the data and perform calculations. I'd be grateful for any suggestions on how I can do this. Paul |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Dates
Hi Paul,
=DATE(RIGHT(A1,2)+2000,MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4)) -- Kind regards, Niek Otten Microsoft MVP - Excel "Trazza_UK" wrote in message ups.com... | Hi there, | | I have inherited a worksheet whereby dates have been recorded in the | following way: | | 311007 for 31st October 2007 | 11107 for 1st November 2007 etc. | | I now need to convert these cells into a date format so I can sort the | data and perform calculations. I'd be grateful for any suggestions on | how I can do this. | | Paul | |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Dates
In B1:
=TEXT(A1,"000000") and copy down Now everything in column B is 6 characters, not 5/6 Then in C1: =DATE(2000+RIGHT(B1,2),MID(B1,3,2),LEFT(B1,2)) and copy down. -- Gary''s Student - gsnu2007a "Trazza_UK" wrote: Hi there, I have inherited a worksheet whereby dates have been recorded in the following way: 311007 for 31st October 2007 11107 for 1st November 2007 etc. I now need to convert these cells into a date format so I can sort the data and perform calculations. I'd be grateful for any suggestions on how I can do this. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting Dates | Excel Worksheet Functions | |||
Converting Julian Dates to regular dates | Excel Worksheet Functions | |||
Converting Dates | Excel Worksheet Functions | |||
Converting Dates | Excel Worksheet Functions | |||
Converting dates | New Users to Excel |