+ Reply to Thread
Results 1 to 19 of 19

Cell contains a date then returns a certain text

  1. 09-27-2021, 02:29 AM #1
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Cell contains a date then returns a certain text

    Hi

    I have a column that contains lots of dates of when my building permits have been lodged and I'm trying to figure out a formula that can help me in another column that I use for invoicing.
    So when a date is entered in my Building permit column is returns the word "Ready" in my invoice column

    Any help will greatly appreciated.

    Thanks
    Em
    Reply With QuoteRegister To Reply
  2. 09-27-2021, 02:38 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Cell contains a date then returns a certain text

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 09-27-2021, 02:52 AM #3
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: Cell contains a date then returns a certain text

    Perhaps if your dates are in col A, in the invoice column something like
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If not, by all means follow Ali's advice
    Reply With QuoteRegister To Reply
  4. 09-27-2021, 05:27 AM #4
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    This is perfect thanks
    Reply With QuoteRegister To Reply
  5. 09-27-2021, 06:14 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Cell contains a date then returns a certain text

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  6. 10-05-2021, 12:53 AM #6
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    Hi
    I've used the above formula mentioned above however it also picks up if the cells contains a word, so ideally I need the formula to only pick up a date format. I've attached an example.
    Any assistance would be great
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-05-2021, 01:00 AM #7
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Cell contains a date then returns a certain text

    Try in L2:

    =IFERROR(IF(I2+0>40000,"READY",""),"")

    Copy to M2
    Quang PT
    Reply With QuoteRegister To Reply
  8. 10-05-2021, 02:57 AM #8
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    Thank you

    Just realised I also need the formula to also include if the word "READY" as well as the date in the BP & OP Column.
    Reply With QuoteRegister To Reply
  9. 10-05-2021, 03:14 AM #9
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: Cell contains a date then returns a certain text

    In which column should READY appear ( please use Column letters in your explanations, not the headers as OP and BP are also columns) ?
    Can the word "READY" also be part of a string ( you have ADMT READY in some cells)
    Also please give us ALL your requirements, instead of adding them one at a time. Thx
    Reply With QuoteRegister To Reply
  10. 10-05-2021, 09:52 PM #10
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    Sorry Pepe I'm not very clear at explaining things, hopefully the below makes sense

    Ok so if columns I & J contain a DATE and/or contain the word READY then I need the word INV READY to reflect in columns L & M.
    At the moment formula =IF(I2<>"","INV READY","") in column L & M is picking up all data in columns I J

    Thanks for your help
    I've attached a new example register
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 10-06-2021, 02:35 AM #11
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: Cell contains a date then returns a certain text

    Something like
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    in I2, pulled right and down ?
    Reply With QuoteRegister To Reply
  12. 10-06-2021, 10:26 PM #12
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    Thanks Pepe for the amended formula

    I've added this formula to column L & M however its still not picking up if columns I & J contain the word ready

    I've attached an amended example register
    Thankyou
    Attached Files Attached Files
    Last edited by emmagizer; 10-07-2021 at 09:16 AM.
    Reply With QuoteRegister To Reply
  13. 10-08-2021, 11:58 AM #13
    JeteMc
    JeteMc is online now
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,560

    Re: Cell contains a date then returns a certain text

    Try the following modification of Pepe's formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  14. 10-14-2021, 10:03 AM #14
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    Excellent, this is great. Thanks JeteMc
    Reply With QuoteRegister To Reply
  15. 10-14-2021, 07:56 PM #15
    JeteMc
    JeteMc is online now
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,560

    Re: Cell contains a date then returns a certain text

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
  16. 10-18-2021, 09:39 PM #16
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    Hi
    Back again, I need to make modifications to your excellent formula

    If in column I & J the cell contains N/A or - then I need this to reflect in column L & M

    I've attached another example sheet with the cells highlight in pink/red

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  17. 10-19-2021, 09:39 AM #17
    JeteMc
    JeteMc is online now
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,560

    Re: Cell contains a date then returns a certain text

    Try pasting the following into cell L2, dragging the fill handle over to cell M2 and then, while L2:M2 are still selected, double click the fill handle to copy down to row 41:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Reply With QuoteRegister To Reply
  18. 10-19-2021, 11:25 PM #18
    emmagizer
    emmagizer is offline
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    70

    Re: Cell contains a date then returns a certain text

    Perfect, thankyou so much
    Reply With QuoteRegister To Reply
  19. 10-20-2021, 02:52 AM #19
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Cell contains a date then returns a certain text

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    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. VLOOKUP formula that matches a date and returns sequential text from a separate worksheet
    By kdestef1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-28-2018, 02:42 PM
  2. [SOLVED] Formula to check if a date falls within a date range, then returns a corresponding cell
    By pjsween in forum Excel General
    Replies: 7
    Last Post: 07-14-2017, 12:04 PM
  3. [SOLVED] finds certain text in a column and returns specified text into a cell
    By jcambell in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-30-2015, 03:35 PM
  4. Set cell format from a UDF that returns a date
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-28-2015, 04:36 PM
  5. Cell returns value if another cell fits within a date range
    By kellyla88 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 07:19 AM
  6. Date Value Returns a Number in Cell
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2009, 01:42 PM
  7. [SOLVED] IF function which returns the text from a cell
    By Wilhelm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-06-2006, 04:14 AM

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