Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date conversion
I want to convert a number i.e. 041006 into a date, having
formatted the new cell as date: dd/mm/yyy. When I use the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2) I get the date 06/10/1904, when it should be 06/10/2004. Any suggestions please |
#2
|
|||
|
|||
Almost. Excel is interpreting "04" as 1904. Try:
=DATE("20"&LEFT(A13,2),MID(A13,3,2),RIGHT(A13,2)) Good for dates 2000 and after. HTH Jason Atlanta, GA -----Original Message----- I want to convert a number i.e. 041006 into a date, having formatted the new cell as date: dd/mm/yyy. When I use the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2) I get the date 06/10/1904, when it should be 06/10/2004. Any suggestions please . |
#3
|
|||
|
|||
Hi
This is a little confusing; if you have 041006 as a real number, it should not display the leading 0. amd your left and mid function would err. Give this a try: =DATE(1900+MOD(A1,100)+100*(MOD(A1,100)<50),MOD(IN T(A1/100),100),INT(A1/1000 0)) The 50 means 49 = 2049 50 = 1950 change to suit your needs. HTH. Best wishes Harald "Gerrym" skrev i melding ... I want to convert a number i.e. 041006 into a date, having formatted the new cell as date: dd/mm/yyy. When I use the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2) I get the date 06/10/1904, when it should be 06/10/2004. Any suggestions please |
#4
|
|||
|
|||
You could also include a test for the 1900's. This is a technique we started
using in the 90's as the millennium approached =DATE(IF(--(LEFT(D13,2))70,19,20)&LEFT(D13,2),MID(D13,3,2),R IGHT(D13,2)) -- HTH RP (remove nothere from the email address if mailing direct) "Jason Morin" wrote in message ... Almost. Excel is interpreting "04" as 1904. Try: =DATE("20"&LEFT(A13,2),MID(A13,3,2),RIGHT(A13,2)) Good for dates 2000 and after. HTH Jason Atlanta, GA -----Original Message----- I want to convert a number i.e. 041006 into a date, having formatted the new cell as date: dd/mm/yyy. When I use the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2) I get the date 06/10/1904, when it should be 06/10/2004. Any suggestions please . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Disable automatic date conversion feature | Excel Worksheet Functions |