Dates

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

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.
 
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.
Click to expand...

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,

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
Click to expand...

Hi again,

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

/Daniel
 
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&IF(G9<>"",TEXT(G9,"dd/mm/yy"),"")

or

=E9&IF(G9<>""," "&TEXT(G9,"dd/mm/yy"),"")
 
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")
Click to expand...
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
Click to expand...

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"))
Click to expand...
 
Back
Top