CONCATENATE and IF statements using dates?

[hissonrr]

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

 

[Jonmo1]

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.

 

[NARAYANK991]

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.

 

[hissonrr]

Thanks it worked perfectly.

I appreciate the help.

-R

 

[Jonmo1]

=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'.

 

[Jonmo1]

You're welcome.

 

[NARAYANK991]

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.