CONCATENATE and IF statements using dates?

Thanks:  0
Likes:  0

Thread: CONCATENATE and IF statements using dates?

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

3. 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. Re: CONCATENATE and IF statements using dates?

Thanks it worked perfectly.

I appreciate the help.

-R

5. Re: CONCATENATE and IF statements using dates?

Originally Posted by NARAYANK991
=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'.

6. Re: CONCATENATE and IF statements using dates?

You're welcome.

7. Re: CONCATENATE and IF statements using dates?

Originally Posted by Jonmo1
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.

User Tag List

Posting Permissions

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