|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 20th, 2009, 12:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to add date and time to a single cell in MS Excel 2007
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 
|
|
#2
April 20th, 2009, 01:07 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to add date and time to a single cell in MS Excel 2007
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
|
|
#3
April 20th, 2009, 01:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to add date and time to a single cell in MS Excel 2007
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
|
|
#4
April 20th, 2009, 02:30 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to add date and time to a single cell in MS Excel 2007
=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
|
|
#5
April 20th, 2009, 06:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to add date and time to a single cell in MS Excel 2007
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 news  =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
|
|
#6
April 21st, 2009, 07:24 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to add date and time to a single cell in MS Excel 2007
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
