Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to convert the dates from the YY:DD forma to MM/dd/YYYY format
Hello,
I have a column with dates in the format YY:DD [here DD stands for day of the year]. Is there a way to have a column that represents the same dates in the normal MM/dd/YYYY format? [MM=month#, dd=day# (day of the month), YYYY=year] Thank you! |
#2
|
|||
|
|||
One way:
Assuming that 2-digit years <=30 are 21st century and years 30 are 20th century: =DATE(--(IF(--LEFT(A1,2)<=30,"20","19")&LEFT(A1,2)),1,--MID(A1,4,3)) Note: I assumed that DD could also be DDD for day of the year over 99. In article , Sam wrote: Hello, I have a column with dates in the format YY:DD [here DD stands for day of the year]. Is there a way to have a column that represents the same dates in the normal MM/dd/YYYY format? [MM=month#, dd=day# (day of the month), YYYY=year] Thank you! |
#3
|
|||
|
|||
So 05:32 = 1 Feb 2005?
If so, then assuming that all dates are after 1 Jan 2000, and all YY are 2 digits (ie 2005 is always 05 and never just 5), and your date to convert is in A1, then this should work ... =DATE(VALUE(LEFT(A1,2))+2000,1,1)+MID(A1,4,3)-1 Rgds, ScottO "Sam" wrote in message ... | Hello, | | I have a column with dates in the format YY:DD [here DD stands for day of | the year]. Is there a way to have a column that represents the same dates in | the normal MM/dd/YYYY format? | [MM=month#, dd=day# (day of the month), YYYY=year] | | Thank you! |
#4
|
|||
|
|||
Thank you very much for your reply! However, I am still experiencing
problems... I noticed that my date column YY:DD has a SPACE in front of YY. Once I remove the space, the formula below works perfectly. How can I remove this space? Thank you!! "JE McGimpsey" wrote: One way: Assuming that 2-digit years <=30 are 21st century and years 30 are 20th century: =DATE(--(IF(--LEFT(A1,2)<=30,"20","19")&LEFT(A1,2)),1,--MID(A1,4,3)) Note: I assumed that DD could also be DDD for day of the year over 99. In article , Sam wrote: Hello, I have a column with dates in the format YY:DD [here DD stands for day of the year]. Is there a way to have a column that represents the same dates in the normal MM/dd/YYYY format? [MM=month#, dd=day# (day of the month), YYYY=year] Thank you! |
#5
|
|||
|
|||
Hi Sam
You could just modify JE's formula to remove the spaces =DATE(--(IF(--LEFT(TRIM(A1),2)<=30,"20","19")&LEFT(TRIM(A1),2)), 1,--MID(TRIM(A1),4,3)) -- Regards Roger Govier "Sam" wrote in message ... Thank you very much for your reply! However, I am still experiencing problems... I noticed that my date column YY:DD has a SPACE in front of YY. Once I remove the space, the formula below works perfectly. How can I remove this space? Thank you!! "JE McGimpsey" wrote: One way: Assuming that 2-digit years <=30 are 21st century and years 30 are 20th century: =DATE(--(IF(--LEFT(A1,2)<=30,"20","19")&LEFT(A1,2)),1,--MID(A1,4,3)) Note: I assumed that DD could also be DDD for day of the year over 99. In article , Sam wrote: Hello, I have a column with dates in the format YY:DD [here DD stands for day of the year]. Is there a way to have a column that represents the same dates in the normal MM/dd/YYYY format? [MM=month#, dd=day# (day of the month), YYYY=year] Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert dates to text keeping the format? | Excel Discussion (Misc queries) | |||
Convert three separate columns of values to dates | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Convert text to dates | Excel Worksheet Functions |