Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel concatenate: Date & Time into DateTime 1
- Thread starter Earth
- Start date
DaleWatson123321
Programmer
Earth,
If you're not familiar with creating "custom" formats, this should be of further help...
First follow euskadi's advise - of adding a formula. Specifically, in D2 for example, simply use =B2+C2.
Then format D2 as follows...
1) From the menu, use Format Cells.
2) In the Format Cells window, under "Category", click on "Custom".
3) Still in the Format Cells window, under "Type", enter the following: h:mm AM/PM - mmmm, dd, yyyy .
The "hyphen" in the above example is optional, as are the number of "spaces" between the Time and Date. I just wanted to provide an example that would point out the "flexibility" of this "custom" formatting.
The above will work, but there is one possibility that will cause it NOT to work. That is IF your "date" entry was made in such a manner that caused BOTH the date and time to be entered in that cell. ONLY the date will show in the date cell because you have "formatted" the cell to show ONLY "date".
However the cell still contain Date and Time. Therefore, in such a case, Excel will have a problem, and will show a "Type Mismatch" error - because the formula in D2 is being asked to add TWO times.
The same problem would arise if you were to enter BOTH the Date and Time in your "time" cell (C2) - for example by having used =NOW() and converting that to a value, but then formatting it to show ONLY the time.
The "bottom line" is that you must ensure that ONLY the Date is entered into the Date column, and ONLY the Time is entered into the Time column.
Hope this helps.
Please advise as to how you make out.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
If you're not familiar with creating "custom" formats, this should be of further help...
First follow euskadi's advise - of adding a formula. Specifically, in D2 for example, simply use =B2+C2.
Then format D2 as follows...
1) From the menu, use Format Cells.
2) In the Format Cells window, under "Category", click on "Custom".
3) Still in the Format Cells window, under "Type", enter the following: h:mm AM/PM - mmmm, dd, yyyy .
The "hyphen" in the above example is optional, as are the number of "spaces" between the Time and Date. I just wanted to provide an example that would point out the "flexibility" of this "custom" formatting.
The above will work, but there is one possibility that will cause it NOT to work. That is IF your "date" entry was made in such a manner that caused BOTH the date and time to be entered in that cell. ONLY the date will show in the date cell because you have "formatted" the cell to show ONLY "date".
However the cell still contain Date and Time. Therefore, in such a case, Excel will have a problem, and will show a "Type Mismatch" error - because the formula in D2 is being asked to add TWO times.
The same problem would arise if you were to enter BOTH the Date and Time in your "time" cell (C2) - for example by having used =NOW() and converting that to a value, but then formatting it to show ONLY the time.
The "bottom line" is that you must ensure that ONLY the Date is entered into the Date column, and ONLY the Time is entered into the Time column.
Hope this helps.
Please advise as to how you make out.Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
-
1
- Thread starter
- #4
Dale & Euskadi,
Thanks for your replies.
It works perfectly (ie. just adding the fields in a formula - regardless of what the cells in the results column are formatted to be). For some reason, I didn't think it would be that simple! I had attempted to use string concatenation etc but had no luck.
Thanks again.
Thanks for your replies.
It works perfectly (ie. just adding the fields in a formula - regardless of what the cells in the results column are formatted to be). For some reason, I didn't think it would be that simple! I had attempted to use string concatenation etc but had no luck.
Thanks again.
- Status
Similar threads