Convert Date to number?

How would i convert a date to number as a string

e.g. 10/05/2005 > "20050510"

note the month is 2 digit - 05

i cant see Format(A1,"yyyy,mm,dd") & i tried = year(a1) & month(a1) &
day(a1)

but it returns month as single "2005510"

p.s. i also tried cell formatting as yyyymmdd, then = "Hello" & A1 - but it
doesn't import the format to a string


"JethroUK©" <[email protected]> wrote in message
news:[email protected]...
> How would i convert a date to number as a string
>
> e.g. 10/05/2005 > "20050510"
>
> note the month is 2 digit - 05
>
> i cant see Format(A1,"yyyy,mm,dd") & i tried = year(a1) & month(a1) &
> day(a1)
>
> but it returns month as single "2005510"
>
>

You can try with TEXT, like this:
=TEXT(A1,"yyyymmdd")

Miguel.

"JethroUK©" wrote:

> p.s. i also tried cell formatting as yyyymmdd, then = "Hello" & A1 - but it
> doesn't import the format to a string
>
>
> "JethroUK©" <[email protected]> wrote in message
> news:[email protected]...
> > How would i convert a date to number as a string
> >
> > e.g. 10/05/2005 > "20050510"
> >
> > note the month is 2 digit - 05
> >
> > i cant see Format(A1,"yyyy,mm,dd") & i tried = year(a1) & month(a1) &
> > day(a1)
> >
> > but it returns month as single "2005510"
> >
> >
>
>
>

There may be better ways but:

=TEXT(YEAR(E10),"####")&TEXT(MONTH(E10),"0#")&TEXT(DAY(E10),"0#")

returns what you are looking for
--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"JethroUK©" <[email protected]> wrote in message
news:[email protected]...
> How would i convert a date to number as a string
>
> e.g. 10/05/2005 > "20050510"
>
> note the month is 2 digit - 05
>
> i cant see Format(A1,"yyyy,mm,dd") & i tried = year(a1) & month(a1) &
> day(a1)
>
> but it returns month as single "2005510"
>
>

"Miguel Zapico" <[email protected]> wrote in message
news:[email protected]...
> You can try with TEXT, like this:
> =TEXT(A1,"yyyymmdd")

There I told you there may be better ways! <g>
--

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk

that's fine

so how will i emmbed this in a string e.g.

="Bill" & TEXT(A1,"yyyymmdd") & "Ben"



"Miguel Zapico" <[email protected]> wrote in message
news:[email protected]...
> You can try with TEXT, like this:
> =TEXT(A1,"yyyymmdd")
>
> Miguel.
>
> "JethroUK©" wrote:
>
> > p.s. i also tried cell formatting as yyyymmdd, then = "Hello" & A1 - but
it
> > doesn't import the format to a string
> >
> >
> > "JethroUK©" <[email protected]> wrote in message
> > news:[email protected]...
> > > How would i convert a date to number as a string
> > >
> > > e.g. 10/05/2005 > "20050510"
> > >
> > > note the month is 2 digit - 05
> > >
> > > i cant see Format(A1,"yyyy,mm,dd") & i tried = year(a1) & month(a1) &
> > > day(a1)
> > >
> > > but it returns month as single "2005510"
> > >
> > >
> >
> >
> >

works fine - thanks


"JethroUK©" <[email protected]> wrote in message
news:[email protected]...
> that's fine
>
> so how will i emmbed this in a string e.g.
>
> ="Bill" & TEXT(A1,"yyyymmdd") & "Ben"
>
>
>
> "Miguel Zapico" <[email protected]> wrote in message
> news:[email protected]...
> > You can try with TEXT, like this:
> > =TEXT(A1,"yyyymmdd")
> >
> > Miguel.
> >
> > "JethroUK©" wrote:
> >
> > > p.s. i also tried cell formatting as yyyymmdd, then = "Hello" & A1 -
but
> it
> > > doesn't import the format to a string
> > >
> > >
> > > "JethroUK©" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > How would i convert a date to number as a string
> > > >
> > > > e.g. 10/05/2005 > "20050510"
> > > >
> > > > note the month is 2 digit - 05
> > > >
> > > > i cant see Format(A1,"yyyy,mm,dd") & i tried = year(a1) & month(a1)
&
> > > > day(a1)
> > > >
> > > > but it returns month as single "2005510"
> > > >
> > > >
> > >
> > >
> > >
>
>