Converting text to date (please help!)

  • Thread starter Thread starter yunity
  • Start date Start date
Y

yunity

Hi all,

How do I convert what appears to be text in the following format:

6/10/2004 8:12:41PM

into a regular date such as 6/10/2004? Also, what is the formula for
calculating the number of days between two dates?

Thanks,

Katie
 
=DATEVALUE(A1) will give you the date portion.

If you need the time, also, the formula is a bit more complicated because
Excel demands a space between the number and the AM/PM, so you have to insert
that:

=TIMEVALUE(LEFT(A1,LEN(A1)-2)&" "&RIGHT(A1,2))

To get the whole thing into an Excel date and time,

=DATEVALUE(A1)+TIMEVALUE(LEFT(A1,LEN(A1)-2)&" "&RIGHT(A1,2))

To calculate the number of days between two dates, just subtract.
 
That doesn't work as you wrote it, Frank. Again, the hassle is that in her
dates there's no space before the AM/PM.
 
Hi Myrna
good spot (did not recognize this missing space -> though this also
could be just a typo by the OP). Using your suggestion one could use
=MOD(--LEFT(A1,LEN(A1)-2)&" "&RIGHT(A1,2)),1)
 
missing space.... though this also could be just a typo by the OP
Click to expand...

Agreed. I've been bitten by this space issue more than once. The space isn't
natural syntax here in the US, IMO.
 
With the string in cell A1:

=DATEVALUE(LEFT(A1,SEARCH(" ",A1)-3))

Stan Scott
New York City
 
Hi Katie,

Re Your first query ... Click on the cell you want the value changing for
.... Go to Format, Cells and click on Date. Youi can gthen select the date
style you wish and incorporate the time or not.



The Answer to your second question is failry starighforward. There's a
DAYS360 function that calcualtes the number of day between two dates based
on a 360 day year. Now I've yet to puizzle that one out ...

But I used the following formula "TODAY()-DATE(2004,1,1)+1" in a cell, that
worked out the day number for this year assuming January the first is day 1.
If you want another date instead of todays date then use the following

"DATE(YEAR,MONTH,DAY)-DATE(YEAR,MONTH,DAY)+1"


Both those formula need to be behind an = sign of course in a cell and drop
the inverted commas.


Pete
 
Back
Top