How to Merge Date from 3 columns to 1

  • Thread starter Thread starter john.bedford3
  • Start date Start date
J

john.bedford3

I have a spreadsheet where the date is spread over 3 columns I,J & K and I
wish to merge them to Column B in the format dd-mmm-yyyy as shown below.

B I J K
02-Feb-2003 02 Feb 2003
27-Mar-1999 27 Mar 1999
01-Apr-1978 Apr 1978
01-Jan-1998 1998

14-Jun-2002 14 Jun 2002

Where there is a blank for the day in 'Column I' I need it to copy as 01 and
where there is a blank for the month in 'Column J' I need it to copy as Jan.
Where all 3 columns 'I' 'J' and 'K' are blank I need it to leave 'Column B'
blank.
Is there a way to do this with a formula I can enter in 'Column B' and copy
down the column. I have been puzzling over this all day.

Thank you.
 
One way:

=IF(COUNTA(I1:K1)=0,"",--(IF(J1="","jan",J1)&" "&IF(I1="",1,I1)
&", "&IF(K1="",2005,K1)))

(all one cell).

Then format the column the way you want:
format|cells|number tab|custom| dd-mmm-yyyy

The --() stuff converts that text string to a number.
 
Have a look in HELP index for DATE. Then substitute the cell address for the
parameters
=date(j2,etc
 
Hi John,

=IF(AND(K1="",J1="",I1=""),"",(IF(I1="","01-",TEXT(I1,"00")&"-"))&(IF(J1="",
"Jan-",J1&"-")&K1))

This creates a text date, not a date date.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thank you Dave that works a treat but could you explain something to me. Why
is the figure 2005 in the last nested IF function or could it be any 4 digit
number?
 
I wasn't sure what you wanted if the year was empty. So I just used the current
year.

You can put anything you want--just like you did with Day and Month.



john.bedford3 said:
Thank you Dave that works a treat but could you explain something to me. Why
is the figure 2005 in the last nested IF function or could it be any 4 digit
number?
Click to expand...
 
If Dave's assumption is correct, you should use YEAR(TODAY()) not 2005, as
next year it will be 2006.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks Don I will have to decide which method is best to use in this
instance.

I appreciate the help.
 
Thanks Bob. I am not sure yet whether being text or an actual date will
matter in this instance. I have Don's solution as well so I can change this
if it is a problem. I have also noted about using YEAR(TODAY()) if this is
required.

Thanks for all your help.
 
Hi Don, I tried this using =DATE(K2,J2,I2). This did not work until I
changed the data in column J to numbers for example '2' instead of 'Feb' .
Having done that it worked fine if all three columns have an entry but not
if one or more columns were blank so I adapted it as follows:

=DATE(IF(K2=0,0,K2),IF(J2=0,1,J2),IF(I2=0,1,I2))

This returns the date 01-Jan-2000 as I require where columns I & J are empty
and K =2000. This also works if column I is blank and J = 1. However, I can
not seem to work out how to return a blank cell where the year in column K
is blank.

Any help would be gratefully appreciated.
 
Tut, tut. I told you about this :-)

=DATE(IF(K1=0,YEAR(TODAY()),K1),IF(J1=0,1,J1),IF(I1=0,1,I1))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
No, that returns the date 1-Jan-2005. I want the formula to return a blank
cell where the year is blank. :-)

Thanks anyway Bob, I appreciate you taking the time.
 
Try this then

=IF(K1=0,"",DATE(K1,IF(J1=0,1,J1),IF(I1=0,1,I1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks Bob. It is remarkably simple once you have the answer.

Many thanks for your help.
 
Back
Top