Date format

[Guest]

Please could someone assist.

I have some data submitted to me, which on the screen is displayed as
1926:04:12 (which is the year:month:date), though excel is holding the data
as a time field, in the background which is 20/03/1900 06:04:12.

How do i capture the true data, I have tried various format changes, though
excel believes its the time i am changing not the values which i am seeing on
the screen.

any help would be appreciated.

 

[Bob Phillips]

That format is [h]:mm:ss, just change it to dd/mm/yyyy hh:mm:ss

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

Are you sure Bob?

1926:04:12 reformatted to General gives 80.2529166666667

which of course when it is formatted as dd/mm/yyy becomes 20/03/1900

Personally I don't think that it can be done but then I was found to be
wrong once <g>

--
Regards,


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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

That format is [h]:mm:ss, just change it to dd/mm/yyyy hh:mm:ss

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Please could someone assist.

I have some data submitted to me, which on the screen is displayed as
1926:04:12 (which is the year:month:date), though excel is holding the
data
as a time field, in the background which is 20/03/1900 06:04:12.

How do i capture the true data, I have tried various format changes,
though
excel believes its the time i am changing not the values which i am
seeing on
the screen.

any help would be appreciated.

 

[Guest]

thanks for the reply,

though the format change changes the displayed data from 1926:04:12 to
20/3/1900 06:04:12.

The data as i see it, is the figures i require - ideally i would want to
change the data (1926:04:12) to a text string for future analysis, elsewhere,
to read 19260412.

the problem is the way the data has been submitted to me and the format of
the cell. excel is reading the data as a time field and not as it is
displayed.

That format is [h]:mm:ss, just change it to dd/mm/yyyy hh:mm:ss

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Please could someone assist.

I have some data submitted to me, which on the screen is displayed as
1926:04:12 (which is the year:month:date), though excel is holding the
data
as a time field, in the background which is 20/03/1900 06:04:12.

How do i capture the true data, I have tried various format changes,
though
excel believes its the time i am changing not the values which i am seeing
on
the screen.

any help would be appreciated.

 

[David Biddulph]

=DATE(INT(A1*24),MINUTE(A1),SECOND(A1))

 

[Sandy Mann]

=DATE(INT(A1*24),MINUTE(A1),SECOND(A1))

So I'm wrong again! <g>

Well done.

--

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk