MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date/Time


Posted by Justin Forbes on July 31, 2001 10:20 AM

I have 2 columns. One has the date '23-jul-01' This is recognized as a valid date in Excel. The second column has the time '22:00' and this is recognized as a valid time. What I need to do is combine it into a single column with a script. I tried using '=TEXT(A1, "DD/MM/YYYY") & " " & TEXT(B1, "HH:MM")'.

This gives me '23-jul-01 22:00' but this is not recognized as a valid date/time. I have also tried using CONCATENATE but this converts the final date/time to a decimal. Can anyone give me a way to join the 2 or convert the decimal to a text date/time and still have it recognized as a date/time?

Thank you,
-------------
Justin Forbes


Posted by IML on July 31, 2001 10:39 AM

How about
=A1+B1
with a format of
d-mmm-yy h:mm

That survived a quick test of my data, but definately double check it.

Good luck

Posted by Mark W. on July 31, 2001 10:39 AM

Use =A1+B1

...and format the results as...

dd-mmm-yy hh:mm

Posted by Justin Forbes on July 31, 2001 11:09 AM

Wow.. I guesse when you get tied up in some stuff, you just overlook the obvious :)..

Thanks guys..