Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

Are the dates in date format. Can you try this formula in a new worksheet.
Enter the dates using Ctrl + semicolon and then edit.

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

Perhaps cell N2 has been formatted as Text, so your entry for Agreed
Date looks like a date but isn't.

Hope this helps.

Pete

On Aug 11, 2:44*pm, winnie123
wrote:
Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why..

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong

Thanks
Winnie

Hi Jacob,

I created a new worksheet and entered the dates with Ctrl + semicolan and
the formula works.

Why is that??

The dates are currently imported from the network system we use. I have set
both columns to date format.

How can I overcome this without having to manually enter the dates?

Thanks for your assistance

"Jacob Skaria" wrote:

Are the dates in date format. Can you try this formula in a new worksheet.
Enter the dates using Ctrl + semicolon and then edit.

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

I am not sure whether this will work..(as desired)

--Can you pre-format the fields to date format...

--OR try the formula..(again depends on the text format of the date text
format)
=IF(DATEVALUE(N2)<DATEVALUE(J2),"Early",IF(N2=J2," On Time","Late"))

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi Jacob,

I created a new worksheet and entered the dates with Ctrl + semicolan and
the formula works.

Why is that??

The dates are currently imported from the network system we use. I have set
both columns to date format.

How can I overcome this without having to manually enter the dates?

Thanks for your assistance

"Jacob Skaria" wrote:

Are the dates in date format. Can you try this formula in a new worksheet.
Enter the dates using Ctrl + semicolon and then edit.

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

To check whether N2 and J2 are both real dates, try =ISNUMBER(N2) and
=ISNUMBER(J2)
My suspicion is that =ISNUMBER(J2) will be FALSE (and =ISTEXT(J2) would be
TRUE).
You might get away with changing your formula from
=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))
to
=IF(N2<--J2,"Early",IF(N2=--J2,"On Time","Late"))
--
David Biddulph


"winnie123" wrote in message
...
Hi,

I have the following formula with which I am trying to calculate if we
have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw
up
my manually entering the date in column J but still get Early as the
answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

Thanks I think the problem must be with the mask I have created to import the
data.

"Jacob Skaria" wrote:

I am not sure whether this will work..(as desired)

--Can you pre-format the fields to date format...

--OR try the formula..(again depends on the text format of the date text
format)
=IF(DATEVALUE(N2)<DATEVALUE(J2),"Early",IF(N2=J2," On Time","Late"))

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi Jacob,

I created a new worksheet and entered the dates with Ctrl + semicolan and
the formula works.

Why is that??

The dates are currently imported from the network system we use. I have set
both columns to date format.

How can I overcome this without having to manually enter the dates?

Thanks for your assistance

"Jacob Skaria" wrote:

Are the dates in date format. Can you try this formula in a new worksheet.
Enter the dates using Ctrl + semicolon and then edit.

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

Pete,

I thinks its the mask I have created to import the records. The date is set
to Charachter and not date format. So will redo my mask and then hopefully it
will work

Thanks

"Pete_UK" wrote:

Perhaps cell N2 has been formatted as Text, so your entry for Agreed
Date looks like a date but isn't.

Hope this helps.

Pete

On Aug 11, 2:44 pm, winnie123
wrote:
Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why..

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong

Thanks
Winnie

On Tue, 11 Aug 2009 06:44:06 -0700, winnie123
wrote:

Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

Most likely a problem with your data.

It may look like a date, but really be text. You can tell by
=ISTEXT(cell_ref). A date should -- FALSE
--ron

Changing the *format* of a cell doesn't change its content, but merely
affects the way a number is *displayed*. If you've got text instead of a
number, changing format will have no effect.
If you need to change values from text to a real date, you will see a number
of suggestions in the archives of this group.

You might try Data/ Text to Columns/ and specify the relevant date format
when you get to the last stage of the wizard.
Another option is to copy a cell containing zer, and using Edit/ Paste
Special/ Add (or copy a 1, and use Edit/ Paste Special/ Multiply).

In the short term, though, if J2 is text have you tried my suggestion of
=IF(N2<--J2,... ?
--
David Biddulph

"winnie123" wrote in message
...
Hi Jacob,

I created a new worksheet and entered the dates with Ctrl + semicolan and
the formula works.

Why is that??

The dates are currently imported from the network system we use. I have
set
both columns to date format.

How can I overcome this without having to manually enter the dates?

Thanks for your assistance

"Jacob Skaria" wrote:

Are the dates in date format. Can you try this formula in a new
worksheet.
Enter the dates using Ctrl + semicolon and then edit.

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi,

I have the following formula with which I am trying to calculate if we
have
delivered on time. The result keeps giving me Early and I am not sure
why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index
and
Match

I have tried to see if the array formula is making the calculation
screw up
my manually entering the date in column J but still get Early as the
answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

Fine. If that doesnt work let us know the date format..If the incoming date
format is all numerics with separators try =IF(N2+0)<(J2+0)...

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Thanks I think the problem must be with the mask I have created to import the
data.

"Jacob Skaria" wrote:

I am not sure whether this will work..(as desired)

--Can you pre-format the fields to date format...

--OR try the formula..(again depends on the text format of the date text
format)
=IF(DATEVALUE(N2)<DATEVALUE(J2),"Early",IF(N2=J2," On Time","Late"))

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi Jacob,

I created a new worksheet and entered the dates with Ctrl + semicolan and
the formula works.

Why is that??

The dates are currently imported from the network system we use. I have set
both columns to date format.

How can I overcome this without having to manually enter the dates?

Thanks for your assistance

"Jacob Skaria" wrote:

Are the dates in date format. Can you try this formula in a new worksheet.
Enter the dates using Ctrl + semicolon and then edit.

If this post helps click Yes
---------------
Jacob Skaria


"winnie123" wrote:

Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

Your suggestion worked by putting -- in front of the cell.

I have also redone my import mask.

Thank you to everyone for your contributions, this has been annoying me all
day.



"David Biddulph" wrote:

To check whether N2 and J2 are both real dates, try =ISNUMBER(N2) and
=ISNUMBER(J2)
My suspicion is that =ISNUMBER(J2) will be FALSE (and =ISTEXT(J2) would be
TRUE).
You might get away with changing your formula from
=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))
to
=IF(N2<--J2,"Early",IF(N2=--J2,"On Time","Late"))
--
David Biddulph


"winnie123" wrote in message
...
Hi,

I have the following formula with which I am trying to calculate if we
have
delivered on time. The result keeps giving me Early and I am not sure why.

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw
up
my manually entering the date in column J but still get Early as the
answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong


Thanks
Winnie

You're welcome, Winnie - thanks for feeding back.

Pete

On Aug 11, 3:44*pm, winnie123
wrote:
Pete,

I thinks its the mask I have created to import the records. The date is set
to Charachter and not date format. So will redo my mask and then hopefully it
will work

Thanks



"Pete_UK" wrote:
Perhaps cell N2 has been formatted as Text, so your entry for Agreed
Date looks like a date but isn't.

Hope this helps.

Pete

On Aug 11, 2:44 pm, winnie123
wrote:
Hi,

I have the following formula with which I am trying to calculate if we have
delivered on time. The result keeps giving me Early and I am not sure why..

=IF(N2<J2,"Early",IF(N2=J2,"On Time","Late"))

Colum N is the Agreed promised Date
Column J is the invoice Date which is an array formula that uses Index and
Match

I have tried to see if the array formula is making the calculation screw up
my manually entering the date in column J but still get Early as the answer,
Even though I put a date later than the Agreed Date.

I have checked to make sure Calculation mode is Automatic

Any Ideas on what I have done wrong

Thanks
Winnie- Hide quoted text -

- Show quoted text -