Concatenate 2 columns date & time

Hi,  I am working with Excel 2003 and have a ws that I need to combine column 
B which is a date, with column C which contains the time.  No matter how I 
have formatted column D, I cannot get the date to display correctly.

What I have is:  B                              C                 
                       05/01/2010               10:55
                       24/12/2009               09:35

I need column D to show as:  05/01/2010 10:55
                                           24/12/2009 09:35

I have tried several different formulas/formatting in Column D but always 
end up with the result in Column D as:  40183 10:55
                                              40171 09:35

I am hoping someone can help solve my problem.

Many Thanks




-- 
Linda
0
Utf
1/9/2010 4:45:01 PM
excel.programming 6508 articles. 1 followers. Follow

3 Replies
3667 Views

Similar Articles

[PageSpeed] 50

You need to format each one
=text(a1,????) & " " & text(b1,?????????)

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"mathel" <mathel@discussions.microsoft.com> wrote in message 
news:357C5DEE-04EC-4468-A1F9-8CB0C34AE628@microsoft.com...
> Hi,  I am working with Excel 2003 and have a ws that I need to combine 
> column
> B which is a date, with column C which contains the time.  No matter how I
> have formatted column D, I cannot get the date to display correctly.
>
> What I have is:  B                              C
>                       05/01/2010               10:55
>                       24/12/2009               09:35
>
> I need column D to show as:  05/01/2010 10:55
>                                           24/12/2009 09:35
>
> I have tried several different formulas/formatting in Column D but always
> end up with the result in Column D as:  40183 10:55
>                                              40171 09:35
>
> I am hoping someone can help solve my problem.
>
> Many Thanks
>
>
>
>
> -- 
> Linda 

0
Don
1/9/2010 5:00:51 PM
If the data in B is a real date (not text) and in C a real time (not text) 
then you do not need to concatenate but simple add: =B1+C1
For how Excel stores date and time see 
http://www.cpearson.com/excel/datetime.htm

Failing that, try =TEXT(B1,"dd/mm/yyyy")&" "&TEXT(C1,"hh:mm")
best wishes


"mathel" <mathel@discussions.microsoft.com> wrote in message 
news:357C5DEE-04EC-4468-A1F9-8CB0C34AE628@microsoft.com...
> Hi,  I am working with Excel 2003 and have a ws that I need to combine 
> column
> B which is a date, with column C which contains the time.  No matter how I
> have formatted column D, I cannot get the date to display correctly.
>
> What I have is:  B                              C
>                       05/01/2010               10:55
>                       24/12/2009               09:35
>
> I need column D to show as:  05/01/2010 10:55
>                                           24/12/2009 09:35
>
> I have tried several different formulas/formatting in Column D but always
> end up with the result in Column D as:  40183 10:55
>                                              40171 09:35
>
> I am hoping someone can help solve my problem.
>
> Many Thanks
>
>
>
>
> -- 
> Linda 

0
Bernard
1/9/2010 5:09:42 PM
Dates and times are stored as numbers in Excel... the date is the number of 
days past "date zero" (which for Excel worksheets, but not VBA, was December 
31, 1899), so the first date available in Excel (assuming you are not using 
the 1904 system) is January 1, 1900 (offset one day from "date zero")... 
your January 5, 2010 date is 40183 days offset from "date zero". This offset 
number is what Excel stores for your date... when you tell Excel to format 
the cell as a Date, it makes the display "human readable" as a date, but 
Excel never stores the date as you see it. This date offset method of 
storing a date is what give Excel the flexibility to display a date in 
whatever format you want. As for time values, they are stored as fractional 
numbers (the decimal part of a floating point number) and are simply the 
fraction of a 24-day that the time represents; so, 3:00pm would be 15 hours 
into the 24-day and Excel would store it as 15/24 which equals 0.625... that 
is the number that Excel sees when you tell it a time value is 3:00pm. Your 
10:55am time on your example data's first row would be seen by Excel as 
(10+55/60)/24 which is 0.454861111 to nine decimal places. That means your 
January 5, 2010 date at 10:55am would be stored as 40183+0.454861111 or, in 
normal form, as 40183.454861111.

Okay, I went through all of the above so you can understand why 
concatenation of date and time values won't work (well, there is a way to do 
it with concatenations, but it is longer and less efficient than the proper 
way)... they are numbers and, as the next to the last step in my first 
paragraph shows, they just need to be added together. So, the formula you 
should have in Column D (say second row for this example) is this...

=B2+C2

and then format the cell as a date in whatever date format you want.

-- 
Rick (MVP - Excel)


"mathel" <mathel@discussions.microsoft.com> wrote in message 
news:357C5DEE-04EC-4468-A1F9-8CB0C34AE628@microsoft.com...
> Hi,  I am working with Excel 2003 and have a ws that I need to combine 
> column
> B which is a date, with column C which contains the time.  No matter how I
> have formatted column D, I cannot get the date to display correctly.
>
> What I have is:  B                              C
>                       05/01/2010               10:55
>                       24/12/2009               09:35
>
> I need column D to show as:  05/01/2010 10:55
>                                           24/12/2009 09:35
>
> I have tried several different formulas/formatting in Column D but always
> end up with the result in Column D as:  40183 10:55
>                                              40171 09:35
>
> I am hoping someone can help solve my problem.
>
> Many Thanks
>
>
>
>
> -- 
> Linda 

0
Rick
1/9/2010 5:10:07 PM
Reply:

Similar Artilces:

Can someone help me??? Try #2
Let me start off by saying that I am no where near an excel expert so please bear with me. I have a schedule for my employees which is attatched to this post as an html file. They are contained in three different sheets within a workbook. Each sheet has its own formulas and such but I have excluded them and also condensed them down to save repitition. The 3 scheduling sheets are named Servers, Hostess, and BarBus I also have/want 7 other sheets named Mon thru Sun and a template is attatched as an html file as well. What I need to get working is the Mon thru Sun sheets to automatically pu...

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

Excel 2003, problem with UK date format using OLE
I have a C++ package that sends data to a spreadsheet using OLE. The package works okay in Excel 97 and Excel 2000 systems, but has a problem with Excel 2003. In Excel 2003 the "Date" column is not recognised as a date when the "Locale" is "UK" (left as "General"), but date is recognised okay when the "Locale" is "US". If I just manually enter the date in "UK" it is recognised okay. I have tried formatting the column according to the "Locale" ("MM/DD/YY" or "DD/MM/YY" or "YY/MM/DD"...

Modal on Modeless #2
I have created several modeless forms for my app, all with the desktop set as parent by using dlg.create(IDD_DLG,GetDesktopWindow()). I did this because I want the user to be able to have each window open, only allow one instance of each window, and allow the user to switch between windows and the main form easily by using taskbar icons. I would like to have a button on one of these modeless windows that will pop up a modal window, that is only modal to that modeless window, so that they can not go back without closing the modal window, though they can still go to any of the other modeless...

Exchange 2007 Evaluation version #2
Can I install it on 64 bit platform and run it as production? On Fri, 23 Feb 2007 09:55:13 -0800, RG <RG@discussions.microsoft.com> wrote: >Can I install it on 64 bit platform and run it as production? There isn't an evaluation version as such. You can install Exchange 2007 without a key for 120 days. ...

worker thread #2
I want to run function in a worker thread: AfxBeginThread(MyDlg::Draw, NULL); Function Draw is declared as protected class member function: UINT Draw( LPVOID pParam ); I have an error during compiling: error C2665: 'AfxBeginThread' : none of the 2 overloads can convert parameter 1 from type 'unsigned int (void *)' What can I run Draw function in a worker thread? ArtuS "ArtuS" <aszeja@gazeta.pl> wrote in message news:c9i826$b9d$1@inews.gazeta.pl... > I want to run function in a worker thread: > AfxBeginThread(MyDlg::Draw, NULL); > > Function Dr...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Xml2PDF formatter version 2.2 is released
Xml2PDF formatter version 2.2 March 30, 2005 Altsoft N.V. Altsoft N.V. announces the release of version 2.2 of its Xml2PDF formatting engine. New XSL-FO features are: - complete support for Unicode BiDi algorithm; - support for soft-hyphen based hyphenation; - improved performance and memory usage; - support for PDF and EPS as external graphics formats; - added control over maximal images resolution (all images above a fixed resolution are downsampled); New SVG features are: - support for markers; - support for symbols; - improved performance and memory usage; New XHTML features a...

Convert text to numbers #2
I have a large spreadsheet with a column that has the numbers formatted as text. I tried reformatting the column from numbers to text but when I sum the column with other columns (that are formatted as numbers) it still doesn't show in the sum. The spreadsheet is too big for me to retype the numbers. Can anyone please help??? In a another column insert a formula that multiplies the first colum that you are having problems with by 1. i.e = a1*1 and drag all the way down. This will force Excel to create a result that is a number. Then copy this new results column on top of the old one...

Printing List of Attachments #2
When I print out a sent email message (Outlook XP) it does not tell me on the printout what attachments were on the message. I don't want the attachments to print, just a list of them Any ideas? It used to! On Fri, 25 Feb 2005 15:53:14 -0000, "Keith" <@.> wrote: >When I print out a sent email message (Outlook XP) it does not tell me on >the printout what attachments were on the message. > >I don't want the attachments to print, just a list of them > >Any ideas? It used to! > Before printing the message, change its format to plain text an...

A program is trying to access your Address Book #2
Since downloading the SP3 patch for Outlook 2002, I'm getting messages that oblige me to manually elect to send messages or use names found in my Address Book. For example, "A program is trying to access your Address Book". Is there a way to elect out of getting these messages every time one emails? The program is allowing to grant permission for periods up to 10 minutes... but that is woefully inadequate. Thanks for your help. B Touhy Check the following for more information about the enhanced security model that protects Outlook. http://www.slipstick.com/outlook/esecup.h...

SQl Trigger #2
Does anyone have any documentation for creating a SQL Trigger? Here is a link to the MSDN site at Microsoft: http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax) "tommyvcfs" <tommyvcfs@discussions.microsoft.com> wrote in message news:2DDAF3DE-8B57-4DD0-9BA0-24DE6AE3119B@microsoft.com... > Does anyone have any documentation for creating a SQL Trigger? PERFECT! THANK YOU Tommyvcfs &q...

Memory Usage #2
Hi, I have developed an application which monitors traffic around a city centre car park. The application works fine as intended and I have been testing it lately. One thing I have noticed which is starting to concern me is that when I start the application its memory usage is around 8.5M in task manager. If I leave it to run overnight, it rises to 18M. I have been careful of releasing everything I have allocated and I don't get no memory leaks reported in Visual Studio. Firstly, what things could be responsible for this increase in memory? Secondly, how can I detect and fix thes...

Memories of Lotus 1-2-3
Way back in the deep dark recesses of my memory I seem to recall some way of extracting all unique values of a certain field from a database in Lotus 1-2-3 to a different portion of the spreadsheet. I have a need to do the same thing now, but like most of the world, I am using Excel now. I can't seem to figure out how to get this done in Excel...in Lotus...I think it was with a data - extract command. Thanks for the help !! Put a label cell on top of the column containing the data Click on the label cell and then pulldown: Data > Filter > Advanced Filter... Check Copy to a...

Sorting 2 lists
I'm sure there's a name for what I'm trying to do, but I don't know it so I can't search previous questions for answers. Please help! I have 2 lists: List 1, column A - Customer names List 1, column B - annual sales 2003 List 2, column C - Customer names (some of which are also on List 1) List 2, column D - annual sales 2004 I want to match up customers that exist in both lists and be able to compare 2003 sales in a column right next to 2004 sales. The customers that don't match up can be listed below. Hi, Am I correct that the two lists are on the same sheet...

linking #2
how do you link sheets together, like i have i the totals in 5 or 6 different sheets and on the last sheet i want the grand total. i need help!! Its pretty simple! You can simply write a formula which looks like this =SUM(Jan:Apr!D3) where, Jan, Feb, Mar and Apr are 4 sheets and i am writing the formula in another sheet named Total. If your data is exaly on the same location in all sheets, you can simply use this formula instead of saying =Jan!B4+Feb!B4+Mar!B4+Apr!B4 Hope this helps. ...

How to #2
Sorry but I am not familiar at all with Publisher. I have a client that needs to put an (*.jpeg) image onto an envelope as the Return address. Can someone familiar with the procedure inform me. Thank you -- Peter Please reply to Newsgroup for the benefit of others. File, Page setup, envelope (various sizes available), insert, picture... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Peter Foldes" <okf22@email.msn.com> wrote in message news:uDLJIzVMFHA.2252@TK2MSFTNGP15.phx.gbl... > Sorry but I am not famili...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

upgrade #2
I recently purchased Money 2004, my previous version being Money 98. When I try to restore the backup an error message says that it can't convert the file because it is an incomatible version. What do I need to do to try to transfer all of my data from 98 to 2004? The mother of all FAQs: http://www.bollar.org/msmoney/#Q1. "Jodie" <anonymous@discussions.microsoft.com> wrote in message news:39c601c3e269$fb85f6d0$a601280a@phx.gbl... > I recently purchased Money 2004, my previous version > being Money 98. When I try to restore the backup an error > message says ...

Columns Wrapping on last page
I am working with two columns in a document. On the last section is not that long so it is splitting the text and putting a few sentences in one column and a few in column two. How can I get the text to stay in the one column (with current width) and not wrap to the second column? Keep with next text and keep lines together under paragraph formatting is checked. Thank you in advance for your assistance. Text will move to the second column when the first is "full." You will have to reduce the line spacing or font size (or both) if you want the text to fit the first ...

Reports #2
Hi, does RMS has a report, that will show every Item sold, layaway, work order. for specific day? Thanks -- Reynold Cycle Unsure myself if there is anything more specific than my suggestion, but as for the items sold use the "Item Movement" report and filter on todays date & Sales >0. You can also filter on the date on the Layaway & Work Order reports. Hope this is what you were looking for. -- Jeremy Janisch "Jose" wrote: > Hi, does RMS has a report, that will show every Item sold, layaway, work > order. for specific day? > Thanks > >...

Date driven formula/worksheet
Hello. I've tried this a couple of times in the past and have been unsuccessful and was hoping someone out there has already wrote a formula for it. The question is regarding a date driven schedule. For instance, a lease might be from 11/1/05 thru 10/31/08. I have a full schedule with columns for the periods ending 06/30/05, 06/30/06, 06/30/07 and so on (June 30 year-ends). Is there a formula I could use to calculate the amount of months that will fall within these buckets? I'll add to this throughout the year and was hoping to just put in the time frame and it would automatical...

PNG files #2
i read the discussion about the PNG files... I had the same prob. when i got Publisher 2002, but it was fixed by downloading the SP1 i think it was... but now my dad bought the Publisher 2003 - and i/we still have that stupid problem, and here its quite difficult to get rid of the very large PNG files - I dont know what to do - its VERY irritating that when you save your website - the size of it is nearly the same as the original document, because of the PNG's does the office 2003 SP1 - of 1.april 2004 solve the problem or not ??? nobody seems t know for real... The Service pack ad...

ExcelReport 2.1 Released! #2
ExcelReport 2.1 Released! Please access http://www.ljzsoft.com ExcelReport is an Excel report generator that outputs reports in Microsoft Excel spreadsheet format. If you know how to use Microsoft Excel and write SQL statements, you can use ExcelReport to create all kinds of Excel reports as you need. ExcelReport can read an Excel report template file and an XRF file. The Excel report template file defines the layouts and formatting of a report. The XRF file has some configuration information and SQL statements, describes how to access database. First ExcelReport creates a blank Excel report ...

Passing DB connection object between 2 Process
Hi All, Is it possible to pass the DB connection object (CDatabase) to a different process, such as a COM+ object, rather than passing the user name and password and recreating the connection? Thanks Marco ahhhhh sounds tricky do let me know "Marco Hung" <marco.hungmarco@gmail.com> wrote in message news:%23pIvAPvSFHA.628@tk2msftngp13.phx.gbl... > Hi All, > Is it possible to pass the DB connection object (CDatabase) to a different > process, such as a COM+ object, rather than passing the user name and > password and recreating the connection? > > Thanks ...