Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
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. -- Cheers Ant. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
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. -- If this helps, please click "Yes" <<<<<<<<<<< "Fats" wrote: 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. -- Cheers Ant. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
Try
=IF(LEFT(D13,10)=TEXT(E13,"dd/mm/yyyy"),1,"") -- Jacob (MVP - Excel) "Fats" wrote: 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. -- Cheers Ant. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
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! -------------------- (Ms-Exl-Learner) -------------------- "Fats" wrote: 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. -- Cheers Ant. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
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.... -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Try =IF(LEFT(D13,10)=TEXT(E13,"dd/mm/yyyy"),1,"") -- Jacob (MVP - Excel) "Fats" wrote: 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. -- Cheers Ant. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
=IF(INT(D3)=INT(E103),1,0)
-- Regards Dave Hawley www.ozgrid.com "Fats" wrote in message ... 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. -- Cheers Ant. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
"Fats" wrote:
=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? 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 ----- "Fats" wrote: 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. -- Cheers Ant. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT formula
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. Fats wrote: 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. -- Cheers Ant. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when inserting new worksheets they read right to left not left to. | Setting up and Configuration of Excel | |||
if(or(left Formula | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Question about using the right or left formula | Excel Discussion (Misc queries) | |||
How to change the right-to-left worksheet to left-to-right workshe | Excel Discussion (Misc queries) |