Date Calculation
Posted by Don on December 31, 2001 9:40 AM
I am attempting to convert the text string 121301 to a date value in order to calculate the numbers of days between the current date and that date. Any help in pointing me in the right direction would be appreciated.
Posted by IML on December 31, 2001 10:14 AM
You could use
=DATEVALUE("12-13-01")
or
=DATEVALUE(TEXT(A1,"00-00-00"))
assuming mmddyy format. (ie won't work with 7701 in A1).
Posted by Aladin Akyurek on January 01, 2002 12:01 PM
Ian --
Is Don not looking something like
=TODAY()-DATE(IF(MID(A5,5,1)+0=0,2000+RIGHT(A5,2),1900+RIGHT(A5,2)),LEFT(A5,2),MID(A5,3,2))
where A1 houses a text-formatted six-digit entry?
Aladin
=======
Posted by IML on January 02, 2002 8:43 AM
I think so...
It is apparent that I read problems as well as I type. I missed the diffence part. However I think I'd still stick with
=TODAY()-DATEVALUE(TEXT(A1,"00-00-00"))
and leave the 1900 -2000 to excel to figure out.
Are you corresponding from Istanbul? Hope all is well.
Posted by Aladin Akyurek on January 02, 2002 1:27 PM
Re: I think so...
Yep. You're right. However, you can simply omit the DATEVALUE function.
Also
=TEXT(A1,"00-00-00")+0
instead of
=DATEVALUE(TEXT(A1,"00-00-00"))
Aladin
PS. I turned to the longish formula, because I couldn't get yours to work (because, I now see, I was using wrong date format--that's, European Vs American formatting...)
Posted by IML on January 02, 2002 3:43 PM
even simpler, perfect (nt)
