String from many fields with Date 3

[net123]

I have an Excel 2000 sheet with a few fields. On the right-most field, I want to create a sentence by capturing data from certain fields in that pertinent row, something like this:

="Name: "&A1&" Assigned: "&B1&" For Week: "&C1
[/red]
The C column is Date format (mm/dd/yyyy), but I am getting fromt statement is something like this:

Name: Mike Assigned: Report For Week: 37849

Your assistance would greatly be appreciated.

 

[SkipVought]

Hi,

Well, you ARE getting a Date -- an Excel Date is a number based on 1/1/1900. Today's date is 37895. In Excel you are able to format a date in any number of ways -- but the date is STILL a number like 37895.

="Name: "&A1&" Assigned: "&B1&" For Week: "&MONTH(C1)&"/"&DAY(C1)&"/"&YEAR(C1)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[net123]

Skip:

That worked beautifully!

Thanks.

 

[DaleWatson123321]

net123,

Skip and I sometimes assist each other ;-)

Here's a variation to Skip's formula. This will provide the "exact" format you specified in your example - i.e. where you specified "mm/dd/yyyy".

Having "mm/dd/yyyy" is sometimes a preferred format because it maintains a "constant position" for the MONTH and DAY - by "zero-filling".

="Name: "&A1&" Assigned: "&B1&" For Week: "&TEXT(C1,"mm/dd/yyyy&quot

Therefore the above formula will generate...
Name: Mike Assigned: Report For Week: 08/16/2003

With this type of formula, you also have the other custom date formatting options... which you might want to consider if there might be potential confusion over whether the date (08/04/2003 for example) is read as Aug 4, 2003 or Apr 8, 2003

Here's are variations of the above formula that will provide clarity...

="Name: "&A1&" Assigned: "&B1&" For Week: "&TEXT(C1,"mmm d, yyyy&quot

="Name: "&A1&" Assigned: "&B1&" For Week: "&TEXT(C1,"mmmm d, yyyy&quot

I hope this helps

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Dale,

Why am I NOT surprised that you present a winning alternative. I have failed to arm myself with that alternative, which is a much better solution!

==> * (a SHINING one, I trust!)



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[net123]

Dale:

Good information! Thanks!