+ Reply to Thread
Results 1 to 11 of 11

Combining Text Only if Dates are Filled in

  1. 06-13-2014, 12:24 PM #1
    adorn
    adorn is offline
    Registered User
    Join Date
    06-13-2014
    Posts
    7

    Combining Text Only if Dates are Filled in

    I want to combine several fields into one cell so it displays like:

    Sample Stage
    Initial Demo Date: 11/14/2013
    Most Recent Demo Date: 05/22/2014
    Proposal Date: 05/10/2014
    Contract Date: 06/12/2014

    But only include "Sample Date: mm/dd/yyyy" if there is a date in the cells. My current formula is:

    =C3&" "&D1&": "&TEXT(D3,"mm/dd/yyyy")&" "&E1&": "&TEXT(E3,"mm/dd/yyyy")&" "&F1&": "&TEXT(F3,"mm/dd/yyyy")&" "&G1&": "&TEXT(G3,"mm/dd/yyyy")&""

    The spaces are included so they go down to the next line. I want C# to always be included but the other parts included contingent on if they have a date in the cell. What If statements would I have to add to the formula? Attached Is the excel document with the formulas.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-13-2014, 12:31 PM #2
    adorn
    adorn is offline
    Registered User
    Join Date
    06-13-2014
    Posts
    7

    Re: Combining Text Only if Dates are Filled in

    Changed the formula so there is less cells it has to pull from

    =C3&" "&"Initial Demo Date"&": "&TEXT(D3,"mm/dd/yyyy")&" "&"Most Recent Demo Date"&": "&TEXT(E3,"mm/dd/yyyy")&" "&"Proposal Date"&": "&TEXT(F3,"mm/dd/yyyy")&" "&"Contract Date"&": "&TEXT(G3,"mm/dd/yyyy")&""
    Reply With QuoteRegister To Reply
  3. 06-13-2014, 12:32 PM #3
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Combining Text Only if Dates are Filled in

    looks like this works...
    =C3&" "&D1&": "&IF(D3<>"",TEXT(D3,"mm/dd/yyyy"),"")&" "&E1&": "&IF(E3<>"",TEXT(E3,"mm/dd/yyyy"),"")&" "&F1&": "&IF(F3<>"",TEXT(F3,"mm/dd/yyyy"),"")&" "&G1&": "&IF(G3<>"",TEXT(G3,"mm/dd/yyyy"),"")&""

    EDIT: used your old formula, you posted while i was writing it.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 06-13-2014, 12:38 PM #4
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Combining Text Only if Dates are Filled in

    I adjusted it a little more so your reference cells are locked in...
    =C3&" "&$D$1&": "&IF(D3<>"",TEXT(D3,"mm/dd/yyyy"),"")&" "&$E$1&": "&IF(E3<>"",TEXT(E3,"mm/dd/yyyy"),"")&" "&$F$1&": "&IF(F3<>"",TEXT(F3,"mm/dd/yyyy"),"")&" "&$G$1&": "&IF(G3<>"",TEXT(G3,"mm/dd/yyyy"),"")&""
    Reply With QuoteRegister To Reply
  5. 06-13-2014, 12:40 PM #5
    adorn
    adorn is offline
    Registered User
    Join Date
    06-13-2014
    Posts
    7

    Re: Combining Text Only if Dates are Filled in

    Wow that works great thank you Sambo kid, one more thing what would I have to change to make the D1, E1..etc fields display dependent on a date being in the D3,E3...etc fields? I want to have both options to show my boss. Thanks again.
    Reply With QuoteRegister To Reply
  6. 06-13-2014, 12:46 PM #6
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Combining Text Only if Dates are Filled in

    not sure i follow. Do you mean that if D3, E3 etc are empty you want D1 or E1 etc to be empty too? Or do you mean that if D3 is empty, the formula in B3 would completly drop the "initial Demo Date:" line because no date is in D3?
    Reply With QuoteRegister To Reply
  7. 06-13-2014, 12:47 PM #7
    adorn
    adorn is offline
    Registered User
    Join Date
    06-13-2014
    Posts
    7

    Re: Combining Text Only if Dates are Filled in

    The second one if D3 is empty, the formula in B3 would completly drop the "initial Demo Date:" line because no date is in D3
    Reply With QuoteRegister To Reply
  8. 06-13-2014, 12:53 PM #8
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Combining Text Only if Dates are Filled in

    I think this will do it, just adjusted the locations for the IFs...
    =C3&" "&IF(D3<>"",$D$1&": "&TEXT(D3,"mm/dd/yyyy"),"")&" "&IF(E3<>"",$E$1&": "&TEXT(E3,"mm/dd/yyyy"),"")&" "&IF(F3<>"",$F$1&": "&TEXT(F3,"mm/dd/yyyy"),"")&" "&IF(G3<>"",$G$1&": "&TEXT(G3,"mm/dd/yyyy"),"")&""
    BTW not sure what the last bolded part is for.
    Reply With QuoteRegister To Reply
  9. 06-13-2014, 12:57 PM #9
    adorn
    adorn is offline
    Registered User
    Join Date
    06-13-2014
    Posts
    7

    Re: Combining Text Only if Dates are Filled in

    That worked perfectly thanks again I would add rep again if it let me!
    Reply With QuoteRegister To Reply
  10. 06-13-2014, 12:58 PM #10
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Combining Text Only if Dates are Filled in

    Great. Thanks for the feedback too. don't forget to mark you post as solved using the thread tools at the top of the post (if you haven't already).
    Reply With QuoteRegister To Reply
  11. 06-13-2014, 01:05 PM #11
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Combining Text Only if Dates are Filled in

    I found one last thing that I think will make your formula much easier, replacing the " " with this char(10) will help you if the column width changes, you won't have to keep adding or deleting spaces. So it would look like this...
    =C3&CHAR(10)&IF(D3<>"",$D$1&": "&TEXT(D3,"mm/dd/yyyy"),"")&CHAR(10)&IF(E3<>"",$E$1&": "&TEXT(E3,"mm/dd/yyyy"),"")&CHAR(10)&IF(F3<>"",$F$1&": "&TEXT(F3,"mm/dd/yyyy"),"")&CHAR(10)&IF(G3<>"",$G$1&": "&TEXT(G3,"mm/dd/yyyy"),"")&""
    Character 10 is the equivalent of a carriage return.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. combining text from rows based upon current month dates
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-26-2014, 03:42 PM
  2. How to lookup a set of dates within a column (filled w/dates) & output values accordingl
    By hcsf_25 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2013, 07:55 PM
  3. [SOLVED] if Row A contains text, SUM all filled in from row A, and show number of filled in in A150
    By jeroenheki in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-12-2012, 06:23 AM
  4. entering dates and cells get filled
    By DrDarkMatter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2011, 09:02 PM
  5. [SOLVED] Sorting dates:How do I sort a column filled with dates by Month, Day, then Year?
    By Heather in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1