dates

[afdmello]

I have three columns; each having the date, day and year. How can use a formula to make all three in one cell to represent a date and then drag down the formula for rest.

thank you

Afd

 

[Rick Rothstein]

Jonmo1 your solution provided a #value error

That is because he assumed different columns from the ones you apparently used. Here is his formula corrected for the columns you are actually using...

=(B1&" "&A1&", "&C1)+0

 

[afdmello]

Hey ! sorry I forgot one ampersand...

Afd

 

[Rick Rothstein]

Thanks Rick that was perfect. Could you please explain the role of "+0" in the formula

When you concatenate text together (that is what the & symbols are doing), you get a text string back... text has no "value" other than as a collection of individual characters bundled together. That collection can look like a number or date, but Excel won't treat it as a number or date because it is text. However, Excel will go out of its way to try to make text that looks like a number or date** into a real number or date if you try to use it in a mathematical operation. First we wrap the text in parentheses to "bundle" it together (I think this is so the mathematical operation will apply to all of the text rather than the last text in the concatenation) and then perform a mathematical operation that won't change the value the text represents once it is converted. Adding zero to any value doesn't change that value, hence the +0. We could also multiply by one or put two minus signs in front of the left parenthesis (two minus signs is the same as multiplying by minus 1 twice and minus one times minus one equals plus one). So, in addition to the formula I posted, these would work as well...

=(A1&B1&C1)*1

=--(A1&B1&C1)


** In case you are not aware, to Excel, a date is a floating point number with the integer part representing a number of days offset from "date zero" (December 31, 1899) and the decimal part the fraction of a 24-hour day represented by the time.

 

[Jonmo1]

That is because he assumed different columns from the ones you apparently used.

No I didn't...

columnB ColumnC columnD
10 May 1998
22 june 2001


I want it as 10-May-1998 in one cell of column E.


I want to do this to subtract dates using the dated if function later

thanks

Afd

 

[Rick Rothstein]

That is because he assumed different columns from the ones you apparently used.

No I didn't...

I had seen the OP's response to both you and me prior to my posting that statement. The operative word in my statement was "apparently". In my initial response to the OP, I had actually missed that he said Columns B, C and D, so I wrote my formula using the "standard" A1, B1 and C1 to which the OP responded it worked... hence my assumption he apparently used different columns than you did which of course, as it turns out, was different from what the he had actually initially requested.