 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006create 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. > 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.  SignatureDave Peterson Or the shorter but somehwat incomprehensible =--(J1 & " 1, " & K1) SignatureRegards, Peo Sjoblom > =datevalue(j1 & " 1, " & k1) > [quoted text clipped - 5 lines] > > that other cells would recognize as 5/1/2006? > > Thanks in advance. Thanks to you both! > Or the shorter but somehwat incomprehensible > [quoted text clipped - 15 lines] > > > that other cells would recognize as 5/1/2006? > > > Thanks in advance. > Or the shorter but somehwat incomprehensible > [quoted text clipped - 9 lines] > > > > 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? > > > 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. I'd just use: =a4+1 and drag that formula down. > > Or the shorter but somehwat incomprehensible > > [quoted text clipped - 22 lines] > > > > > > Dave Peterson SignatureDave Peterson > I'd just use: > > =a4+1 > and drag that formula down. Duh! Thanks! > > > Or the shorter but somehwat incomprehensible > > > [quoted text clipped - 22 lines] > > > > > > > > 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 >> Or the shorter but somehwat incomprehensible >> [quoted text clipped - 18 lines] >> > > that other cells would recognize as 5/1/2006? >> > > Thanks in advance. >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 SignatureRegards Roger Govier >>Or the shorter but somehwat incomprehensible >> [quoted text clipped - 5 lines] > > --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 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> SignatureRegards Roger Govier >>Ron >>I like it!!! [quoted text clipped - 12 lines] > > --ron >Hi Ron > [quoted text clipped - 3 lines] >the last part of my posting. >It still in no way detracts from the first 3 lines of my post<vbg> Thank you. --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.