concatenate dates?

[Craig AS]

hey,
i have 3 combo boxes, first with day numbers, second with month names, and third with years. I was just waundering if it is possible to concatenate three cells as a date. I have tried but i cannot solve it. any help is appreciated, thnx alot

Craig

 

[Aladin Akyurek]

On 2002-04-27 08:26, Craig AS wrote:
Hey again,

sorry to bother u again, but i have found a problem with it. for some reason it will not work with the year. #NUM! comes up in the cell. it works without the year, but the year come up as 1990 all the time, so its definetly got something 2 do with the year, but i dont know what, ne ideas?

Craig

Make first selections from your comboboxes.

Now tell me:

Which cell is the cell link of the first combobox? What is its value now?

Which cell is the cell link of the second combobox? What is its value now?

Which cell is the cell link of the third combobox? What is its value now?

PS. How did you create these comboboxes? Did you create them using View|Toolbars|Forms?

Aladin

 

[Craig AS]

ok

first combo box - cell link is B29 and its value is 1

second combo box - cell link C29 its value is 12, but i have a vloopup formaula which looks up this number and then displays the relevent month, so in this cell(C27) it says dec.

third combo box - cell link is D29, its vale is 1, but again i have used a lookup formula, and so in cell D28 it displays 2002.

and yes i did make the combo box from the forms toolbar. Hope this is what u wanted.

Craig

 

[Aladin Akyurek]

On 2002-04-27 09:30, Craig AS wrote:
ok

first combo box - cell link is B29 and its value is 1

second combo box - cell link C29 its value is 12, but i have a vloopup formaula which looks up this number and then displays the relevent month, so in this cell(C27) it says dec.

third combo box - cell link is D29, its vale is 1, but again i have used a lookup formula, and so in cell D28 it displays 2002.

and yes i did make the combo box from the forms toolbar. Hope this is what u wanted.

Craig

What follows what we logically should do:

=DATE(INDEX(Range3,D29),INDEX(Range2,C29),INDEX(Range1,B29))

where Range3 is the Input range of your 3rd combobox (apparently, consisting of 2002,2003,etc), Range2 is the Input range of your 2nd combobox (apparently consisting of 1,2,3,4,... up to 12), and Range1 is the Input range of your 1st combobox (apparently consisting of 1,2,3,... up to 31).

Notice that INDEX does exactly what you do with VLOOKUP.

However, we can shorten the above formula, given the regularity of your Input ranges, just to:

=DATE(INDEX(Range3,D29),C29,B29)

Custom format the cell of the formula as e.g.,

dd-mmm-yy

Aladin
This message was edited by Aladin Akyurek on 2002-04-27 10:07

 

[Craig AS]

hey thnx for the help.

but.....i get #name? coming up now. If u know whats up tell me, thanx alot 4 all the help u been giving me.

Craig

 

[Aladin Akyurek]

On 2002-04-27 13:59, Craig AS wrote:
hey thnx for the help.

but.....i get #name? coming up now. If u know whats up tell me, thanx alot 4 all the help u been giving me.

Craig

I hope you didn't use things like Range3 literally, did you?
This message was edited by Aladin Akyurek on 2002-04-27 14:06

 

[Craig AS]

hey,

what do u mean? used range3 literally? this is the formula i have in excel now:

=DATE(INDEX(,D29),C27,B29)

with this i get #value!
and if i leave range3 in i get #Name?

i bet i look really stupid, lol, ah well...if u cud give ne advice? please. thnx AGAIN!! hehe

Craig
This message was edited by Craig AS on 2002-04-28 03:54

 

[Aladin Akyurek]

On 2002-04-28 03:50, Craig AS wrote:
hey,

what do u mean? used range3 literally? this is the formula i have in excel now:

=DATE(INDEX(,D29),C27,B29)

with this i get #value!
and if i leave range3 in i get #Name?

i bet i look really stupid, lol, ah well...if u cud give ne advice? please. thnx AGAIN!! hehe

Craig
This message was edited by Craig AS on 2002-04-28 03:54

Craig,

Geez... You must substitute the value of the Input range of your third combobox for Range3 in the formula:

=DATE(INDEX(Range3,D29),C27,B29)

Send your workbook to me if you like.

Cheers.

Aladin

 

[nisht]

As i have understood you want day, month and year to appear in one cell.. then .. i suppose file nos 18 'changind combobox' can be useful to you.

ni****h desai
http://www.pexcel.com

 

[Aladin Akyurek]

On 2002-04-28 06:55, nisht wrote:
As i have understood you want day, month and year to appear in one cell.. then .. i suppose file nos 18 'changind combobox' can be useful to you.

ni****h desai
http://www.pexcel.com

I trust you detected error(s) in my description (and also you don't expect much from my offer to Craig that he could send me his workbook).

 

[Craig AS]

hey,

ok i'll send my work, u have wuzip dont u? coz my file wont fit otherwise. Ne way, thanx alot.

Craig