Dates

[Khalil Handal]

Hi,
Cell E9 has text, Cell G9 has date (dd/mm/yy). C9=E9&G9
My problem is that the date is C9 is "converted" to text. So if G9=23/12/09
in Cell C9 it shown as 40170.

What should be done to solve this issue.

 

[Domenic]

Try...

=E9&TEXT(G9,"dd/mm/yy")

or

=E9&" "&TEXT(G9,"dd/mm/yy")

 

[bash]

Hi,
Cell E9 has text, Cell G9 has date (dd/mm/yy). C9=E9&G9
My problem is that the date is C9 is "converted" to text. So if G9=23/12/09
in Cell C9 it shown as 40170.

What should be done to solve this issue.

Hi,

Try using the DATE formula together with RIGHT, MID and LEFT formula:

=DATE(RIGHT(C9,2),MID(C9;4;2),LEFT(C9,2))

Make sure to right click and choose date format and NOT general.

/Daniel

 

[bash]

Hi,

Try using the DATE formula together with RIGHT, MID and LEFT formula:

=DATE(RIGHT(C9,2),MID(C9;4;2),LEFT(C9,2))

Make sure to right click and choose date format and NOT general.

/Daniel

Hi again,

Actually, just right click on C9 and choose date format instead of
general.

/Daniel

 

[Khalil Handal]

This works well eccept when the cell is empty (no value).
It shows 00/01/00 with the text of cell E9. How can this be solved.

i.e. if G9 has no date entered we will have only the value of E9 without
the 00/01/00.

 

[Domenic]

Try...

=E9&IF(G9<>"",TEXT(G9,"dd/mm/yy"),"")

or

=E9&IF(G9<>""," "&TEXT(G9,"dd/mm/yy"),"")

 

[bash]

This works well eccept when the cell is empty (no value).
It shows 00/01/00 with the text of cell E9. How can this be solved.

i.e.  if G9 has no date entered we will have only the value of E9 without
the 00/01/00.

Try...


=E9&" "&TEXT(G9,"dd/mm/yy")

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Hi,

Sorry think I misread your question:

Think you should do like Domenic suggested but add a IF formula if you
don't want dd/mm/yy to be shown:

=IF(G13="",E13,E13&" "&TEXT(G13,"dd/mm/yy"))

 

[Khalil Handal]

This also worked:
=E10&TEXT(G10,(IF(G10="","","dd/mm/yy")))
Thank you.