Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: concatenate dates?

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  2. #12
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  4. #14
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #15
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  6. #16
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #17
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  8. #18
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #19
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

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

  10. #20
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hey,

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

    Craig

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •