In "A1" I got 20060101 10:59, but in I want "B2" to be just 20060101 as result of "A1".
Convert date & time to just date


Re: Convert date & time to just date
In B2, write =A1
and format B2 with the desired format. See click right on B2, choose Format and choose the right date format, with time indications.
Wigi

Re: Convert date & time to just date
To just get the date use INT.
=INT(A1)

Re: Convert date & time to just date
The problem to use Excelformats is that it just hidding the time, but i still in the value. Is possible for just take out the first 10 letters or numbers from A1.
So from 20060101 10:20 it will just take 20060101. 
Re: Convert date & time to just date
I lost you here. What's wrong with the suggestion of Norie?
Wigi


Re: Convert date & time to just date
Perhaps this:
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1)
where A1 has the date.
Or something else?
Wigi

Re: Convert date & time to just date
thank you

Re: Convert date & time to just date
Try
=LEFT(A1,10)

Re: Convert date & time to just date
sjobevakare
Wigi's formula will return a text value which might not be able to be used in further calculations.
Brians12
If it is a true date/time value in A1 that won't return just the date.:)
It will return a number with 5 digits before the decimal place and 4 after it.

Re: Convert date & time to just date
How right you are norie. Sorry, I had never used this formula on a date before. sjobevakare asked about taking the first several characters this popped into my head with out me trying it out.
Sorry


Re: Convert date & time to just date
Brians12
No problem.:)
The formula you posted would work fine on a text date, which you never know might actually be what the OP is dealing with.

Re: Convert date & time to just date
I believe time is stored in a decimal form.
38555 is a whole number standing for 7/22/2005
38556 is a whole number standing for 7/23/2005Time is stored as a fraction of a day.
or 1 / (60 *60 *24) multiplied by the number of seconds in the day.
So 3 am = .125 or 3 hours x 60 minutes x 60 seconds x the constant above.
You can pick up the difference in hours between two values by subtracting and then multiplying by 24 (this will yield something like, as an example, 6.21, which is 6.21 hours. or 6 hours and 60 * .21 or 12.6 minutes or 6 hours 12 minutes and .6 * 60 or 36 seconds.
I think this is right

Re: Convert date & time to just date
Quote from noriesjobevakare
Wigi's formula will return a text value which might not be able to be used in further calculations.
You're totally right Norie, but I did that suggestion because earlier suggestions of you and me did not seem to get the desired result for the OP, which I find a bit strange, but without clarification from the OP's part...
Wigi

Re: Convert date & time to just date
Dear sjobevakare,
I've got the nearly same question and I've got one suggestion that I think it will work for you. You try the formula of Wigi but add one more thing as following:
=DATEVALUE(DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1))
Hope it help.Best regards,
TQV 


Re: Convert date & time to just date
Really, Glenn. Why? (Joking  Joking)
Simplicity is good, but the datevalue one is the one I use. It is easier to manipulate in code. But, if you don't need anything complex....... Shorter is better.
All depends on the need. Just goes to show there are often a dozen ways to get at the same result with VBA.

Re: Convert date & time to just date
Fengore
VBA? Where?:stare:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!