| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I join 3 separate columns of dates
I want to join 3 separate date fields into one cell. Problem is it converts
the date to a serial number. How can I keep the date as a date and not as a seriel number. Example: 50: 16-Nov, 110: Sep-17, 180: 9-Jul Formula: =$C$1&": "&C2&", "&$D$1&": "&D2&", "&$E$1&": "&E2 Result: 50: 39768, 110: 39708, 180: 39638 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I join 3 separate columns of dates
You can use the TEXT function:
=$C$1&": "&TEXT(C2,"d-mmm")&", "&$D$1&": "&TEXT(D2,"d-mmm")&", "&$E$1&": "&TEXT(E2,"d-mmm") HTH Elkar I want to join 3 separate date fields into one cell. Problem is it converts the date to a serial number. How can I keep the date as a date and not as a seriel number. Example: 50: 16-Nov, 110: Sep-17, 180: 9-Jul Formula: =$C$1&": "&C2&", "&$D$1&": "&D2&", "&$E$1&": "&E2 Result: 50: 39768, 110: 39708, 180: 39638 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I join 3 separate columns of dates
=$C$1&": "&TEXT(C2,"mm dd yyyy") etc.
The "mm dd yyyy" is whatever choice you want for display. Gord Dibben MS Excel MVP On Tue, 10 Mar 2009 11:38:02 -0700, H8Workn wrote: I want to join 3 separate date fields into one cell. Problem is it converts the date to a serial number. How can I keep the date as a date and not as a seriel number. Example: 50: 16-Nov, 110: Sep-17, 180: 9-Jul Formula: =$C$1&": "&C2&", "&$D$1&": "&D2&", "&$E$1&": "&E2 Result: 50: 39768, 110: 39708, 180: 39638 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I join 3 separate columns of dates
On Tue, 10 Mar 2009 11:38:02 -0700, H8Workn
wrote: I want to join 3 separate date fields into one cell. Problem is it converts the date to a serial number. How can I keep the date as a date and not as a seriel number. Example: 50: 16-Nov, 110: Sep-17, 180: 9-Jul Formula: =$C$1&": "&C2&", "&$D$1&": "&D2&", "&$E$1&": "&E2 Result: 50: 39768, 110: 39708, 180: 39638 Use the TEXT worksheet function to properly format your output Something like (not tested): =c1&":"&text(c2, "d-mmm") & d1 ..... --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I join 3 separate columns of dates
That worked perfectly...thx for the help!
Ron "Elkar" wrote: You can use the TEXT function: =$C$1&": "&TEXT(C2,"d-mmm")&", "&$D$1&": "&TEXT(D2,"d-mmm")&", "&$E$1&": "&TEXT(E2,"d-mmm") HTH Elkar I want to join 3 separate date fields into one cell. Problem is it converts the date to a serial number. How can I keep the date as a date and not as a seriel number. Example: 50: 16-Nov, 110: Sep-17, 180: 9-Jul Formula: =$C$1&": "&C2&", "&$D$1&": "&D2&", "&$E$1&": "&E2 Result: 50: 39768, 110: 39708, 180: 39638 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I join 3 separate columns of dates
I had someone respond with =$C$1&": "&TEXT(C2,"d-mmm")..which gave me what I
was looking for ...thx "Gord Dibben" wrote: =$C$1&": "&TEXT(C2,"mm dd yyyy") etc. The "mm dd yyyy" is whatever choice you want for display. Gord Dibben MS Excel MVP On Tue, 10 Mar 2009 11:38:02 -0700, H8Workn wrote: I want to join 3 separate date fields into one cell. Problem is it converts the date to a serial number. How can I keep the date as a date and not as a seriel number. Example: 50: 16-Nov, 110: Sep-17, 180: 9-Jul Formula: =$C$1&": "&C2&", "&$D$1&": "&D2&", "&$E$1&": "&E2 Result: 50: 39768, 110: 39708, 180: 39638 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I join 3 separate columns of dates
Got the answer...thx.
"Ron Rosenfeld" wrote: On Tue, 10 Mar 2009 11:38:02 -0700, H8Workn wrote: I want to join 3 separate date fields into one cell. Problem is it converts the date to a serial number. How can I keep the date as a date and not as a seriel number. Example: 50: 16-Nov, 110: Sep-17, 180: 9-Jul Formula: =$C$1&": "&C2&", "&$D$1&": "&D2&", "&$E$1&": "&E2 Result: 50: 39768, 110: 39708, 180: 39638 Use the TEXT worksheet function to properly format your output Something like (not tested): =c1&":"&text(c2, "d-mmm") & d1 ..... --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Join multiple rows into columns | Excel Discussion (Misc queries) | |||
| separate two dates from a text string into two separate cells | Excel Discussion (Misc queries) | |||
| How do I join a date and time that are in separate columns? | Excel Discussion (Misc queries) | |||
| join columns, keep both values | New Users to Excel | |||
| Convert three separate columns of values to dates | Excel Worksheet Functions | |||
Linear Mode
