LEFT formula

  • Thread starter Thread starter Fats
  • Start date Start date
F

Fats

Hello,

Need some assistance on the following:

=IF(LEFT(D103,10)=LEFT(E103,10),"1","")

D103 is a link to a MS Project file cell containing a date and time - eg.
"13/09/2010 11:00:00 AM". E103 is a date - eg. "13/09/2010".

The above formula should produce "1" when the first 10 characters match. How
ever it does not. Can any one see my error or offer a better formula to get
the result I am after?

Thanks for your help in advance.

Ant.
 
Try,
=IF(LEFT(Text(D103,"dd/mm/yyyy"),10)=LEFT(TEXT(E103,"dd/mm/yyyy"),10),"1","")

You need to make sure you are using LEFT on a string, then it should work
perfectly.
 
Try this…

I hope there is no need to use Left function with date Values.

=IF(VALUE(TEXT(D103,"DD/MM/YYYY"))=VALUE(TEXT(E103,"DD/MM/YYYY")),1,"")

Remember to Click Yes, if this post helps!
 
The below formula works only if the D103 is in text format....or else you
will have to use TEXT() function as mentioned in the other posts....
 
Fats said:
=IF(LEFT(D103,10)=LEFT(E103,10),"1","")
D103 is a link to a MS Project file cell containing a
date and time - eg. "13/09/2010 11:00:00 AM".
E103 is a date - eg. "13/09/2010". [....]
Can any one see my error or offer a better formula
to get the result I am after?
Click to expand...

Sometimes, the solution is easier to understand than the explanation. At a
minimum, try:

=if(int(D103) = E103, "1", "")

But why are you returning 1 as a string? I suspect you really (should) want:

=if(int(D103) = E103, 1, "")

As for the explanation....

Remember that what is displayed in the cell is often the cell's true value.
Formats affect the appearance, not the value. For example, the Accounting
format causes a dollar sign and commas to appear (based on my regional
settings; you might see other characters). But those characters are not part
of the value of the cell.

Likewise, the Date and Time formats (as well as related Custom formats)
merely affect the appearance of the cell value.

But in fact, dates are stored as integers representing the number of
calendar days since 12/31/1899; for example, 13 Sep 2010 is the number 40434.
Time is stored as a fraction of a day; for example, noon is 0.5.

With that in mind, if you enter the formulas =LEFT(D103,99) and
=LEFT(E103,99), you will gain some insight into why your formula does not
work.

Alternatively, format D103 and E103 as General or Number with some number of
decimal places to see their true values.

INT(D103) captures the date part of the date/time value. We could also
write INT(E103). But that is unnecessary since you said E103 contains only a
date; thus, time is 0:00:00 AM, which is a decimal fraction of zero. In
other words, E103 is the same value as INT(E103).


----- original message -----
 
You have responses to your question, but I'd be careful.

If that data is brought into General formatted cells, then some of that data
will be brought back as a real date/time.

For instance:
09/12/2010 11:00:00 AM
may come in as Sept 12, 2010 (depending on your windows regional short date
format).

But the data in the original source represents December 9, 2010.

So you may want to check those date/times and compare to what they represent in
the original source.

If you find that they're not all correct, then you have a couple of choices.

One is to return the data as text -- then convert it to a real date/time.

The other may be simpler. Change your windows regional short date format (just
temporarily) to match the order of the original data.

I know that I wouldn't trust the data as it stands now.
 
Back
Top