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

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.
--
David Biddulph

"Anil123" wrote in message
...
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

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
---------------
Jacob Skaria


"Anil123" wrote:

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

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


"Anil123" wrote:

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

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

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

--
Rick (MVP - Excel)


"Teethless mama" wrote in message
...
=TEXT(SUBSTITUTE(A1,".",""),"00-00-0000")+B1


"Anil123" wrote:

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

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

"Anil123" wrote:

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