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

 

[Jonmo1]

Can you post examples of what is in each column?

 

[Datsmart]

If you have Day, Month, Year in cells A1, B1, C1, put this formula in your date cell.

=DATE(C1,B1,A1)

Note: this is for USA date style, adjust as needed for Europe style.

 

[afdmello]

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

 

[Jonmo1]

Try

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

then format the cell with the formula in date format you desire.

Hope that helps.

 

[Rick Rothstein]

Try

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

then format the cell with the formula in date format you desire.

A little simpler construction is possible..

=(A1&B1&C1)+0

 

[afdmello]

John's suggestion worked well except that I have to change the month to a number.

Jonmo1 your solution provided a #value error


Afd

 

[afdmello]

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

Afd

 

[Jonmo1]

Jonmo1 your solution provided a #value error

Afd

Works for me

Excel Workbook
	B	C	D	E	F
1	10	May	1998		10-May-1998
2	22	june	2001		22-Jun-2001
3	7	Dec	2009		7-Dec-2009
4	30	January	1999		30-Jan-1999
Sheet1

 

[Datsmart]

Adding zero lets Excel know it is to be a Number/Date rather than Text.