1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula needed...

Discussion in 'Business Applications' started by shatrughna, Apr 23, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. shatrughna

    shatrughna Thread Starter

    Joined:
    Aug 13, 2008
    Messages:
    157
    Heyy i am in need of a formula in excel 2007.
    I have two columns-one of which contains the name and the other contains the date of arrival (like 21st Apr.).
    Now i want a formula which returns 1 if there is some date in the cell and 0 if the cell contains other than the date.
    can anyone pl guide me???

    thanks in advance.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    would it always have Travel Date in the cell ?
    if so you can use Mid and IF

    =IF( (Mid(C2,1,11)="Travel date"), 1 , 0 )
     
  3. shatrughna

    shatrughna Thread Starter

    Joined:
    Aug 13, 2008
    Messages:
    157

    Its not so that the cell always contains the travel date.
    What i need is,the formula should return 1 if it is some travel date and 0 if it is something else.
    Is it possible??
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    well you don't have a date in the field just TEXT so the test would need to check the type of text that is entered, and looking for a common format - or you need to change the way the data is entered into that field - so only a true date format can be entered

    will the month always be three characters ie JAN, FEB, MAR, APR, MAY,JUN, JUL, AUG, SEP, OCT, NOV, DEC its possible something may be done by searching the field for any of those characters
    before looking into that, i will wait for you to confirm it is 100% entered as three characters as above and numbers are not used or full months - ie 26/4/10 or any other format of date or April instead of Apr
     
  5. shatrughna

    shatrughna Thread Starter

    Joined:
    Aug 13, 2008
    Messages:
    157
    heyyy...ur last formula worked like a charm for me!!
    thanks a lot.
    :):):)
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    you said, that if there is a date in the field - its not always proceeded with the word "travel date" - my formula will only work if the word travel date is first in the cell
     
  7. shatrughna

    shatrughna Thread Starter

    Joined:
    Aug 13, 2008
    Messages:
    157
    Ya thts true.
    in some cells it was "Travel dt." and i just had to change it to "Travel date" to make ths formula work.
    But it was manageable and i have successfully dne it.
    thnx.
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    OK - if you are going to use this a lot and there are only a couple of alternatives then you could use an OR

    some thing like

    =IF( (Mid(C2,1,11)="Travel date"), 1 , 0 )

    =IF( OR( (Mid(C2,1,11)="Travel date") , (Mid(C2,1,9)="Travel dt") ) , 1 , 0 )
    NOT checked on Excel - as not near the machine, so syntax maybe wrong ,,,,,, but you get the idea
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I would highly recommend you splitting up your data across multiple columns. Dates deserve their own cells, which would give you greater flexibility to work with your data. Combining everything in a single cell only spells disaster.
     
  10. shatrughna

    shatrughna Thread Starter

    Joined:
    Aug 13, 2008
    Messages:
    157

    Actually i want to set the status for some persons and hence i need to specify the dates along with the texts.is there any better way??
    thanks.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    That still doesn't tell me why you can't split up the data. Can you give examples of what kind of data you might be looking at or what you're trying to do with it?
     
  12. shatrughna

    shatrughna Thread Starter

    Joined:
    Aug 13, 2008
    Messages:
    157
    actually i need to specify the status of the persons against their names.
    I need to specify the travel dates (if fixed) and if the date is fixed and entered in that cell,i need to have a result "1" so that i can take that cell in account for overall summary.

    u can see a sample attached in my first post here.
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Look, you haven't told me why you can't split up your data amongst multiple cells already. You're making it more difficult by not doing so.
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Why not just do something like this?
     

    Attached Files:

  15. shatrughna

    shatrughna Thread Starter

    Joined:
    Aug 13, 2008
    Messages:
    157
    i have found that etaf's formula has worked best for me coz in that,i dont even need to split my cells or allot a separate column for date values.
    the reason why i dont put a date column is that i dont require the dates for all of the rows.in some cases only i need to specify the dates and that too in the form of remarks as i have given in my sheet.
    Zack's idea would be applicable if i need to specify the dates for all of the candidates.
    in case where there is no date applicable and i leave that cell blank the formula may not work,i think.
    so the formula should work with the combination of text and dates and also when the cell is blank or contains something else than the combination.
    thanks.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918728

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice