CONCATENATE and IF statements using dates?

hissonrr

Board Regular
Joined
Feb 6, 2016
Messages
106
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))
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.
 
Upvote 0
=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'.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top