Convert date(text) to a number

Can you post a few more examples (or a sample workbook)? Then we can see what happens with a single-digit date (like 1st July) - is it 01 or just 1?

Also, isn't 12:00:00 AM the same as 12 noon?

Pete

if its the 1st July then the date is 7/1/2012 12:00:00 AM
And yes 12:00:00 AM is noon, my bad

The meaning of all this is that i need to calculate how much timediff there is between 2 dates: A1=(7/31/2012 12:00:00 AM) and A2=(7/31/2012 12:01:00 AM)
and i can't do A2-A1 because the dates are seen as text not as a number.

ok now i can work with it. thanks

@ Jen Cop

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Yea i forgot, i was at work then. Sorry

---------- Post added at 10:07 AM ---------- Previous post was at 09:04 AM ----------

Ok I now face the following problem:
Cell A1 = 7/31/2012 12:00:00 AM
Text-To-Column:
B1 = 31/7/2012 (=number)
C1 = 12:00:00 (= number)
D1 = AM

But I'm stuck here with 12h day and not a 24h day.
There is no diff between 12 noon and 12 midnight.
12 noon = 31/7/2012 12:00:00
12 midnight = 31/7/2012 12:00:00
I don't think Text-To-Column is right solution then? :s

I used text-to-column abit diff, now i get this
A1= 7/31/2012 12:00:00 AM
B1 = 31/7/2012 (number)
C1 = 12:00:00 AM (format as [h]:mm:ss and it gives this: 0:00:00)
D1 = B1+C1 = 31/7/2012 0:00:00
and my last line is 31/7/2012 23:59:00, so ill mark this as solved now.
I hope it work now in my main file. Thanks all

ps: bensinshiryo, the timevalue doesnt work and long function gives error with me. but thanks anyway.

This is the formula I came up with yesterday while working on this:

=DATE(MID(A1,SEARCH("/",A1,3)+1,4),LEFT(A1,SEARCH("/",A1)-1),SUBSTITUTE(MID(A1,SEARCH("/",A1)+1,2),"/",""))+VALUE(MID(A1,SEARCH(" ",A1)+1,8))+IF(LEFT(RIGHT(A1,2))="P",0.5)

Text date in A1, this formula in B1.

Hope this helps.

Pete