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

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

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/2005

Time 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 norie

sjobevakare

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

Hi TQV,
I'd prefer Norie's formula:

=INT(A1)

to yours, sorry.

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: