how to add date and time to a single cell in MS Excel 2007

[Anil123]

Hi,

Iam trying to add date column ( 01.03.2009) and time column (23:30:00) to a
single cell to get the result as 2/28/2009 23:30 using the formula :

=IF(A1<>"",A1+B1,""), But its throwing an exception of #VALUE!

Please help in this regard.

Thanks,
Anil

 

[David Biddulph]

If you are getting a #VALUE! error, it's because at least one of your
arguments is text, not a number. I guess that your 01.03.2009 is text, so
you need to convert it into an Excel date.

 

[Jacob Skaria]

The below will display in text format
A1 = date
B1 = Time
=TEXT(A1,"mm/dd/yyyy") & " " & TEXT(B1,"hh:mm")

If this post helps click Yes

 

[Teethless mama]

=TEXT(SUBSTITUTE(A1,".",""),"00-00-0000")+B1

 

[Rick Rothstein]

You don't really need the TEXT function call; this seems to work as well...

=SUBSTITUTE(A1,".","-")+B1

 

[Anil123]

Hello All,

Thank you for your solutions, but unfortunately neither of the solutions
worked. the error iam getting is mainly because of the regional and langauge
settings, 01.03.2009 is the date format of switzerland and my PC is with US
settings, so I changed the regional settings and the formula worked out well.

Thanks again for ur quick response.

Anil