create date from text

D

davegb

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.
 
D

davegb

Peo said:
Or the shorter but somehwat incomprehensible

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


--

Regards,

Peo Sjoblom
Click to expand...

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?
 
G

Gord Dibben

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?
Click to expand...

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


Gord Dibben MS Excel MVP
 
R

Roger Govier

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Roger Govier

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>
 
R

Ron Rosenfeld

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>
Click to expand...

Thank you.


--ron
 
Top