Concatenate

[chief.dale]

I am using Concatenate to input 2 dates as follows:
in C2 I have =CONCATENATE($H$2," TO ",$I$2)
H2 and I2 are date inputs.

In C2 I get numbers and not dates.
What am I doing wrong??

 

[T. Valko]

I am using Concatenate to input 2 dates as follows:
in C2 I have =CONCATENATE($H$2," TO ",$I$2)
H2 and I2 are date inputs.

In C2 I get numbers and not dates.
What am I doing wrong??

Try it like this...

=IF(COUNT(H2:I2)<2,"",TEXT(H2,"m/d/yyyy")&" To "&TEXT(I2,"m/d/yyyy"))

Change this expression in both places:

m/d/yyyy

To whatever date format you normally use in your location.

 

[Claymationator]

Welcome to the board!

Give this a try:

=TEXT($H$2,"M/DD/YYYY")&" TO "&TEXT($I$2,"M/DD/YYYY")

Hope that helps.

 

[T. Valko]

P.S.

In C2 I get numbers and not dates.
What am I doing wrong??

You're not doing anything wrong.

In Excel dates are really just numbers formatted to look like dates.

So, when you reference a cell with a date Excel evaluates that cell based on its true value which is the number representing the date.

The numbers are the count of days since a base date. The default base date is Jan 1 1900. Jan 1 1900 has the numeric value of 1. Each successive day the numeric value increases by 1.

So:

Jan 1 1900 = 1
Jan 2 1900 = 2
Jan 3 1900 = 3
Jan 4 1900 = 4
Jan 5 1900 = 5
Feb 10 1995 = 34740
Mar 21 2011 = 40623

You can see the numeric value of a date by changing the cell format to General or Number.

These numeric values are commonly referred to as the date serial numbers.

 

[chief.dale]

Thanks all to the responses.

 

[T. Valko]

Thanks all to the responses.

You're welcome!