If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with the CONCATENATE formula
I would like to CONCATENATE the following phrases on Sheet1!A19 with dates
from Sheet2!D32 and Sheet2!D33 but am having some problems. Please help. I would like to see the following in Sheet1!A19: Your service will expire on April 19, 2009. However, your last preventive maintanence with revalidation was on March 1, 2008. Problem being displayed is the following: Your service will expire on 39922 and your last preventive maintanence with revalidation was on 39508. The dates on Sheet2!D32 and Sheet2!D33 are formated for a Date with the following format - April 19, 2009. -- tech1NJ |
#2
|
|||
|
|||
Help with the CONCATENATE formula
Hi
Use the TEXT function to format the date as desired. ="Your Text "&TEXT(A1;"mmm. dd, yyyy") Hopes this helps --- Per "tech1NJ" skrev i meddelelsen ... I would like to CONCATENATE the following phrases on Sheet1!A19 with dates from Sheet2!D32 and Sheet2!D33 but am having some problems. Please help. I would like to see the following in Sheet1!A19: Your service will expire on April 19, 2009. However, your last preventive maintanence with revalidation was on March 1, 2008. Problem being displayed is the following: Your service will expire on 39922 and your last preventive maintanence with revalidation was on 39508. The dates on Sheet2!D32 and Sheet2!D33 are formated for a Date with the following format - April 19, 2009. -- tech1NJ |
#3
|
|||
|
|||
Help with the CONCATENATE formula
You'll need to use the TEXT function to force the formatting of inserted
dates. Formula then becomes: ="Your service will expire on "&TEXT(Sheet2!D32,"mmmm dd, yyyy")&". However, your last preventive maintanence with revalidation was on "&TEXT(Sheet2!D33,"mmmm dd, yyyy")&"." -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: I would like to CONCATENATE the following phrases on Sheet1!A19 with dates from Sheet2!D32 and Sheet2!D33 but am having some problems. Please help. I would like to see the following in Sheet1!A19: Your service will expire on April 19, 2009. However, your last preventive maintanence with revalidation was on March 1, 2008. Problem being displayed is the following: Your service will expire on 39922 and your last preventive maintanence with revalidation was on 39508. The dates on Sheet2!D32 and Sheet2!D33 are formated for a Date with the following format - April 19, 2009. -- tech1NJ |
#4
|
|||
|
|||
Help with the CONCATENATE formula
The concatenate will pull in the value but not the format. You can dictate
the format using the text function: ="Your service will expire on " & text(Sheet2!D32,"Mmmm d, yyyy") etc "tech1NJ" wrote: I would like to CONCATENATE the following phrases on Sheet1!A19 with dates from Sheet2!D32 and Sheet2!D33 but am having some problems. Please help. I would like to see the following in Sheet1!A19: Your service will expire on April 19, 2009. However, your last preventive maintanence with revalidation was on March 1, 2008. Problem being displayed is the following: Your service will expire on 39922 and your last preventive maintanence with revalidation was on 39508. The dates on Sheet2!D32 and Sheet2!D33 are formated for a Date with the following format - April 19, 2009. -- tech1NJ |
#5
|
|||
|
|||
Help with the CONCATENATE formula
If you convert the date to a text format using the following formula it may
show properly in the concatenate formula. Refernence the cell with the text formula in your concatenate formula (no need to paste special as "value"). =TEXT(F1,"mmmmmmmm dd, yyyy") were F1 is the date you want included. "tech1NJ" wrote: I would like to CONCATENATE the following phrases on Sheet1!A19 with dates from Sheet2!D32 and Sheet2!D33 but am having some problems. Please help. I would like to see the following in Sheet1!A19: Your service will expire on April 19, 2009. However, your last preventive maintanence with revalidation was on March 1, 2008. Problem being displayed is the following: Your service will expire on 39922 and your last preventive maintanence with revalidation was on 39508. The dates on Sheet2!D32 and Sheet2!D33 are formated for a Date with the following format - April 19, 2009. -- tech1NJ |
#6
|
|||
|
|||
Help with the CONCATENATE formula
This worked. Thanks
-- tech1NJ "bapeltzer" wrote: The concatenate will pull in the value but not the format. You can dictate the format using the text function: ="Your service will expire on " & text(Sheet2!D32,"Mmmm d, yyyy") etc "tech1NJ" wrote: I would like to CONCATENATE the following phrases on Sheet1!A19 with dates from Sheet2!D32 and Sheet2!D33 but am having some problems. Please help. I would like to see the following in Sheet1!A19: Your service will expire on April 19, 2009. However, your last preventive maintanence with revalidation was on March 1, 2008. Problem being displayed is the following: Your service will expire on 39922 and your last preventive maintanence with revalidation was on 39508. The dates on Sheet2!D32 and Sheet2!D33 are formated for a Date with the following format - April 19, 2009. -- tech1NJ |
Thread Tools | |
Display Modes | |
|
|