create date from text

I'm trying to create a date in cell A4 from text in cells J1 & K1.
J1="May", K1="2006". Is there a way to create a formula in cell A4
using a "1" for the day, and the month from cell J1, year from cell K1
that other cells would recognize as 5/1/2006?
Thanks in advance.

=datevalue(j1 & " 1, " & k1)

Format it as a date.



davegb wrote:
>
> I'm trying to create a date in cell A4 from text in cells J1 & K1.
> J1="May", K1="2006". Is there a way to create a formula in cell A4
> using a "1" for the day, and the month from cell J1, year from cell K1
> that other cells would recognize as 5/1/2006?
> Thanks in advance.

--

Dave Peterson

Or the shorter but somehwat incomprehensible

=--(J1 & " 1, " & K1)


--

Regards,

Peo Sjoblom

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43C587C9.5AF637A5@verizonXSPAM.net...
> =datevalue(j1 & " 1, " & k1)
>
> Format it as a date.
>
>
>
> davegb wrote:
> >
> > I'm trying to create a date in cell A4 from text in cells J1 & K1.
> > J1="May", K1="2006". Is there a way to create a formula in cell A4
> > using a "1" for the day, and the month from cell J1, year from cell K1
> > that other cells would recognize as 5/1/2006?
> > Thanks in advance.
>
> --
>
> Dave Peterson

Thanks to you both!


Peo Sjoblom wrote:
> Or the shorter but somehwat incomprehensible
>
> =--(J1 & " 1, " & K1)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43C587C9.5AF637A5@verizonXSPAM.net...
> > =datevalue(j1 & " 1, " & k1)
> >
> > Format it as a date.
> >
> >
> >
> > davegb wrote:
> > >
> > > I'm trying to create a date in cell A4 from text in cells J1 & K1.
> > > J1="May", K1="2006". Is there a way to create a formula in cell A4
> > > using a "1" for the day, and the month from cell J1, year from cell K1
> > > that other cells would recognize as 5/1/2006?
> > > Thanks in advance.
> >
> > --
> >
> > Dave Peterson

Peo Sjoblom wrote:
> Or the shorter but somehwat incomprehensible
>
> =--(J1 & " 1, " & K1)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43C587C9.5AF637A5@verizonXSPAM.net...
> > =datevalue(j1 & " 1, " & k1)
> >
> > Format it as a date.
> >
> >

Didn't think to ask this in the orginal post, how do I then get the "1"
to increment as I autofill down for the rest of the month?
> >
> > davegb wrote:
> > >
> > > I'm trying to create a date in cell A4 from text in cells J1 & K1.
> > > J1="May", K1="2006". Is there a way to create a formula in cell A4
> > > using a "1" for the day, and the month from cell J1, year from cell K1
> > > that other cells would recognize as 5/1/2006?
> > > Thanks in advance.
> >
> > --
> >
> > Dave Peterson

I'd just use:

=a4+1
and drag that formula down.




davegb wrote:
>
> Peo Sjoblom wrote:
> > Or the shorter but somehwat incomprehensible
> >
> > =--(J1 & " 1, " & K1)
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:43C587C9.5AF637A5@verizonXSPAM.net...
> > > =datevalue(j1 & " 1, " & k1)
> > >
> > > Format it as a date.
> > >
> > >
>
> Didn't think to ask this in the orginal post, how do I then get the "1"
> to increment as I autofill down for the rest of the month?
> > >
> > > davegb wrote:
> > > >
> > > > I'm trying to create a date in cell A4 from text in cells J1 & K1.
> > > > J1="May", K1="2006". Is there a way to create a formula in cell A4
> > > > using a "1" for the day, and the month from cell J1, year from cell K1
> > > > that other cells would recognize as 5/1/2006?
> > > > Thanks in advance.
> > >
> > > --
> > >
> > > Dave Peterson

--

Dave Peterson

Dave Peterson wrote:
> I'd just use:
>
> =a4+1
> and drag that formula down.

Duh! Thanks!

>
>
>
>
> davegb wrote:
> >
> > Peo Sjoblom wrote:
> > > Or the shorter but somehwat incomprehensible
> > >
> > > =--(J1 & " 1, " & K1)
> > >
> > >
> > > --
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:43C587C9.5AF637A5@verizonXSPAM.net...
> > > > =datevalue(j1 & " 1, " & k1)
> > > >
> > > > Format it as a date.
> > > >
> > > >
> >
> > Didn't think to ask this in the orginal post, how do I then get the "1"
> > to increment as I autofill down for the rest of the month?
> > > >
> > > > davegb wrote:
> > > > >
> > > > > I'm trying to create a date in cell A4 from text in cells J1 & K1.
> > > > > J1="May", K1="2006". Is there a way to create a formula in cell A4
> > > > > using a "1" for the day, and the month from cell J1, year from cell K1
> > > > > that other cells would recognize as 5/1/2006?
> > > > > Thanks in advance.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
>
> --
>
> Dave Peterson

dave

>Didn't think to ask this in the orginal post, how do I then get the "1"
>to increment as I autofill down for the rest of the month?

=--(J1 & ROW() & " , " & K1)


Gord Dibben MS Excel MVP

On 11 Jan 2006 14:59:46 -0800, "davegb" <davegb@safebrowse.com> wrote:

>
>Peo Sjoblom wrote:
>> Or the shorter but somehwat incomprehensible
>>
>> =--(J1 & " 1, " & K1)
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:43C587C9.5AF637A5@verizonXSPAM.net...
>> > =datevalue(j1 & " 1, " & k1)
>> >
>> > Format it as a date.
>> >
>> >
>
>Didn't think to ask this in the orginal post, how do I then get the "1"
>to increment as I autofill down for the rest of the month?
>> >
>> > davegb wrote:
>> > >
>> > > I'm trying to create a date in cell A4 from text in cells J1 & K1.
>> > > J1="May", K1="2006". Is there a way to create a formula in cell A4
>> > > using a "1" for the day, and the month from cell J1, year from cell K1
>> > > that other cells would recognize as 5/1/2006?
>> > > Thanks in advance.
>> >
>> > --
>> >
>> > Dave Peterson

On Wed, 11 Jan 2006 14:40:51 -0800, "Peo Sjoblom" <terre08@mvps.org> wrote:

>Or the shorter but somehwat incomprehensible
>
>=--(J1 & " 1, " & K1)


Or the even shorter:

=--(J1&K1)


--ron

Ron
I like it!!!
Can't get any shorter (or easier) than that.
Just have to reverse the order of J1 and K1 for UK formats

--
Regards

Roger Govier


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:gcgbs1hnbanuie4feqijiimqc6mfntth0c@4ax.com...
> On Wed, 11 Jan 2006 14:40:51 -0800, "Peo Sjoblom" <terre08@mvps.org>
> wrote:
>
>>Or the shorter but somehwat incomprehensible
>>
>>=--(J1 & " 1, " & K1)
>
>
> Or the even shorter:
>
> =--(J1&K1)
>
>
> --ron

On Thu, 12 Jan 2006 03:04:45 -0000, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> wrote:

>Ron
>I like it!!!
>Can't get any shorter (or easier) than that.
>Just have to reverse the order of J1 and K1 for UK formats

Roger, I just checked that and I don't believe you have to reverse it.

A three letter month and a four digit year are unambiguous, so should be
interpreted correctly when entered in MonthYear order.

I find the J1&K1 (with J1=month and k1=year) get interpreted correctly no
matter if I set my regional settings to UK or US.



--ron

Hi Ron

It must have been the lateness of the hour (or the earliest!!!) but the
brain read K as Month and June as Year.
As I got into bed, it occurred to me that I had totally screwed up in
the last part of my posting.
It still in no way detracts from the first 3 lines of my post<vbg>

--
Regards

Roger Govier


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:holbs1pp32cadefqdqorhngsrh6r9u1tk4@4ax.com...
> On Thu, 12 Jan 2006 03:04:45 -0000, "Roger Govier"
> <roger@technologyNOSPAM4u.co.uk> wrote:
>
>>Ron
>>I like it!!!
>>Can't get any shorter (or easier) than that.
>>Just have to reverse the order of J1 and K1 for UK formats
>
> Roger, I just checked that and I don't believe you have to reverse it.
>
> A three letter month and a four digit year are unambiguous, so should
> be
> interpreted correctly when entered in MonthYear order.
>
> I find the J1&K1 (with J1=month and k1=year) get interpreted correctly
> no
> matter if I set my regional settings to UK or US.
>
>
>
> --ron

On Thu, 12 Jan 2006 10:36:19 -0000, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> wrote:

>Hi Ron
>
>It must have been the lateness of the hour (or the earliest!!!) but the
>brain read K as Month and June as Year.
>As I got into bed, it occurred to me that I had totally screwed up in
>the last part of my posting.
>It still in no way detracts from the first 3 lines of my post<vbg>

Thank you.


--ron