Dates


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.
 
Ad

Advertisements

B

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
 
B

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
 
K

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.
 
D

Domenic

Try...

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

or

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

Advertisements

B

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"))
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top