Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate 2 columns date & time
You need to format each one
=text(a1,????) & " " & text(b1,?????????) -- Don Guillett Microsoft MVP Excel SalesAid Software "mathel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate 2 columns date & time
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate 2 columns date & time
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 calculate time between 2 date/time columns | Excel Discussion (Misc queries) | |||
concatenate time from 3 columns | Excel Discussion (Misc queries) | |||
subtracting two date/time columns to get total time | Excel Programming | |||
Is there a way to concatenate date and time? | Excel Discussion (Misc queries) | |||
How do I concatenate two columns when one is in date format? | Excel Discussion (Misc queries) |