Excel Formula needed...

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
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 )
 

shatrughna

Thread Starter
Joined
Aug 13, 2008
Messages
157
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 )

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??
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
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
 

shatrughna

Thread Starter
Joined
Aug 13, 2008
Messages
157
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
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.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
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
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

shatrughna

Thread Starter
Joined
Aug 13, 2008
Messages
157
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.

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.
 
Joined
Jul 25, 2004
Messages
5,458
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?
 

shatrughna

Thread Starter
Joined
Aug 13, 2008
Messages
157
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?
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top