Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: CONCATENATE and IF statements using dates?

  1. #1
    Board Regular
    Join Date
    Feb 2016
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default CONCATENATE and IF statements using dates?

    Good day all,

    I was making a spreadsheet and ran into an issue that I was not sure how to solve.

    I am using the =CONCATENATE function to write a text string for an email title as well as using =IF statements in it to determine some variable.

    I ran into the problem when I wanted to reference a date in my =IF statement, in short I am trying to make it so "IF(W15=1,TODAY(),K15))" where K15 will be an actual numerical number and not a date. I can make it return numerical numbers but not a date and I am unsure if this is because of my =CONCATENATE function or if it because you can not use an =IF statement to return a date or number and it has to be one or the other.

    anyone know any solutions to this?

    =CONCATENATE(B1," ", G15," ", IF(W15=1,TODAY(),K15))

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: CONCATENATE and IF statements using dates?

    Try

    =CONCATENATE(B1," ", G15," ", IF(W15=1,TEXT(TODAY(),"mm/dd/yyyy"),K15))

    You can reverse the mm/dd to dd/mm depending on your preferences.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Location
    CHENNAI , India
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: CONCATENATE and IF statements using dates?

    Hi ,

    In Excel , dates are essentially numbers ; thus today's date November 7 , 2017 would , in the absence of a date format , display the number 43046.

    Thus , if you want a formula to return a date value in the display , you would have to format it using the TEXT function , as in :

    =CONCATENATE(B1," ", G15," ", TEXT(IF(W15=1,TODAY(),K15),"dd-mm-yyyy"))

    Change the format string dd-mm-yyyy to what ever format you want.

  4. #4
    Board Regular
    Join Date
    Feb 2016
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: CONCATENATE and IF statements using dates?

    Thanks it worked perfectly.

    I appreciate the help.

    -R

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: CONCATENATE and IF statements using dates?

    Quote Originally Posted by NARAYANK991 View Post
    =CONCATENATE(B1," ", G15," ", TEXT(IF(W15=1,TODAY(),K15),"dd-mm-yyyy"))
    OP said K15 is an actual numerical value, NOT a date.
    So the TEXT function should be applied only to the TODAY function, not the result of the IF.
    With your formula, if W15 does not = 1, then it will return K15 'formatted as a date'.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: CONCATENATE and IF statements using dates?

    You're welcome.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    Board Regular
    Join Date
    Jan 2012
    Location
    CHENNAI , India
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: CONCATENATE and IF statements using dates?

    Quote Originally Posted by Jonmo1 View Post
    OP said K15 is an actual numerical value, NOT a date.
    So the TEXT function should be applied only to the TODAY function, not the result of the IF.
    With your formula, if W15 does not = 1, then it will return K15 'formatted as a date'.
    Hi ,

    Yes , my mistake.

Some videos you may like

User Tag List

Tags for this Thread

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
  •