Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date as text
I have a spreadsheet with columns of dates and times. I need to combine
them into a common cell. For example, I have columns A and B, and want to generate C:: A B C 1/1 4:00 1/1-4:00 1/2 4:15 1/2-4:15 1/3 4:25 1/3-4:25 When I use =concatenate(a1,"-",b1) in column C I get: 39083-0.195833333333333 39084-0.196527777777778 39085-0.197222222222222 I've tried TEXT(), but can't figure out what text format string to use. I also tried formatting column C as Text - didn't work I've searched this newsgroup and the MS knowledgebase to no avail. Can anyone help? Thanks, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date as text
Have you tried:
=TEXT(A1+B1,"m/d-h:mm") MH "Invalid" wrote in message ... I have a spreadsheet with columns of dates and times. I need to combine them into a common cell. For example, I have columns A and B, and want to generate C:: A B C 1/1 4:00 1/1-4:00 1/2 4:15 1/2-4:15 1/3 4:25 1/3-4:25 When I use =concatenate(a1,"-",b1) in column C I get: 39083-0.195833333333333 39084-0.196527777777778 39085-0.197222222222222 I've tried TEXT(), but can't figure out what text format string to use. I also tried formatting column C as Text - didn't work I've searched this newsgroup and the MS knowledgebase to no avail. Can anyone help? Thanks, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date as text
Try this:
=TEXT(A1+B1,"m/d-h:mm") Biff "Invalid" wrote in message ... I have a spreadsheet with columns of dates and times. I need to combine them into a common cell. For example, I have columns A and B, and want to generate C:: A B C 1/1 4:00 1/1-4:00 1/2 4:15 1/2-4:15 1/3 4:25 1/3-4:25 When I use =concatenate(a1,"-",b1) in column C I get: 39083-0.195833333333333 39084-0.196527777777778 39085-0.197222222222222 I've tried TEXT(), but can't figure out what text format string to use. I also tried formatting column C as Text - didn't work I've searched this newsgroup and the MS knowledgebase to no avail. Can anyone help? Thanks, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date as text
Figured it out:
=CONCATENATE(TEXT(B2,"m/d")," - ",TEXT(C2,"h:mm")) Sheesh! "Invalid" wrote in message ... I have a spreadsheet with columns of dates and times. I need to combine them into a common cell. For example, I have columns A and B, and want to generate C:: A B C 1/1 4:00 1/1-4:00 1/2 4:15 1/2-4:15 1/3 4:25 1/3-4:25 When I use =concatenate(a1,"-",b1) in column C I get: 39083-0.195833333333333 39084-0.196527777777778 39085-0.197222222222222 I've tried TEXT(), but can't figure out what text format string to use. I also tried formatting column C as Text - didn't work I've searched this newsgroup and the MS knowledgebase to no avail. Can anyone help? Thanks, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date as text
Try this:
=A1+B1 Format cells as: m/d-h:mm "Invalid" wrote: I have a spreadsheet with columns of dates and times. I need to combine them into a common cell. For example, I have columns A and B, and want to generate C:: A B C 1/1 4:00 1/1-4:00 1/2 4:15 1/2-4:15 1/3 4:25 1/3-4:25 When I use =concatenate(a1,"-",b1) in column C I get: 39083-0.195833333333333 39084-0.196527777777778 39085-0.197222222222222 I've tried TEXT(), but can't figure out what text format string to use. I also tried formatting column C as Text - didn't work I've searched this newsgroup and the MS knowledgebase to no avail. Can anyone help? Thanks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenating a Text and a Date without losing orginal Date Format | Excel Discussion (Misc queries) | |||
Help: How do I convert a text date into a real date format | Excel Worksheet Functions | |||
Text to date format | Excel Discussion (Misc queries) | |||
Date from text to Date format | Excel Worksheet Functions | |||
Date to text format | Excel Discussion (Misc queries) |