Hi all

I have a cell with date and time that I want to convert to date only - I
have tried so many things. Can anyone tell me the secret function?

Dates are stored internally as integers (number of elapsed days since
a reference date of 1st Jan 1900) and times are stored as fractions of
a 24-hour day. So, if you have a date-time in A1, this will give you
the date-only part:

=INT(A1)
or =ROUND(A1,0)

Hope this helps.

Pete

On Mar 22, 7:30 pm, canuckinnj
wrote:
Hi all

I have a cell with date and time that I want to convert to date only - I
have tried so many things. Can anyone tell me the secret function?

Pete,

Just a warning to the OP as I am sure you know,

=ROUND(A1,0)

Will give you the date in A1 or the next day depending in the time that is
being held in A1.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Pete_UK" wrote in message
oups.com...
Dates are stored internally as integers (number of elapsed days since
a reference date of 1st Jan 1900) and times are stored as fractions of
a 24-hour day. So, if you have a date-time in A1, this will give you
the date-only part:

=INT(A1)
or =ROUND(A1,0)

Hope this helps.

Pete

On Mar 22, 7:30 pm, canuckinnj
wrote:
Hi all

I have a cell with date and time that I want to convert to date only - I
have tried so many things. Can anyone tell me the secret function?

Yeah, you're right Sandy. I suppose it ought to be:

=ROUNDDOWN(A1,0)

if the OP only wants the date.

Thanks.

Pete

On Mar 22, 7:52 pm, "Sandy Mann" wrote:
Pete,

Just a warning to the OP as I am sure you know,

=ROUND(A1,0)

Will give you the date in A1 or the next day depending in the time that is
being held in A1.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Pete_UK" wrote in message

oups.com...



Dates are stored internally as integers (number of elapsed days since
a reference date of 1st Jan 1900) and times are stored as fractions of
a 24-hour day. So, if you have a date-time in A1, this will give you
the date-only part:

=INT(A1)
or =ROUND(A1,0)

Hope this helps.

Pete

On Mar 22, 7:30 pm, canuckinnj
wrote:
Hi all

I have a cell with date and time that I want to convert to date only - I
have tried so many things. Can anyone tell me the secret function?- Hide quoted text -

- Show quoted text -