• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date as a date vs. date as a serial number

D

DashboardNovice

Member
Cells N7 and O7 have dates which appear as: 10/01/2014 and 10/02/2014. These dates are the result of formulas.

In cell N77 I have: CONCATENATE($D77,N$7) which appears as "Joe10/01/2014" when I use formula evaluator.

In cell O77 I have: CONCATENATE($D77,O$7) which appears as "Joe41914" when I use formula evaluator.

In the first case, date appears as a date, but in the second case, a date appears as a serial number.

For both dates in N7 and O7, I have selected them and applied Short Date format, but I'm still having this problem.

What is causing this? What is the best way to fix this?
 
Hi,
Use the below formatting in your formula:
concatenate($D77,TEXT(O$7,"dd-mmm-yy"))

Regards,
Prasad DN
 
I would suspect that the value in N7 is actually a string, as the formula in O77 is the expected result. For solution, see prasaddn's solution, or to save some typing:
=$D77&TEXT(O$7,"mm/dd/yyyy")
 
You must log in or register to reply here.
Back
Top