Converting text to date (please help!)

[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

 

[Myrna Larson]

=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.

 

[Frank Kabel]

Hi
try
=MOD(--A1,1)
and format as date

 

[Myrna Larson]

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.

 

[Frank Kabel]

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)

 

[Myrna Larson]

missing space.... though this also could be just a typo by the OP

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

 

[Stan Scott]

With the string in cell A1:

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

Stan Scott
New York City

 

[Pete Hawkins]

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