Value error with this formula
 

Hello,

I have the following formula which is currently resulting in an error. It is
checking the shiped date in column N and adding 1 day on against the agreed
despatch date.

IF(OR(R25="Thursday",N25+4<J25,"Early",IF(N25+4=J2 5,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Ea rly",IF(N25+1=J25,"OnTime","Late")))))


Basically I want to check if Cell R25 has a Thursday date and if so add 4
days onto N25, also if N25 does not have any value to return as blank.

Any suggestions please?

Thanks
Winnie

Value error with this formula
 

Sorry,

The original formula is which works

=IF(N25="","",IF(N25+1<J25,"Early",IF(N25+1=J25,"O nTime","Late")))

And now I want to check if the despatch date is a Thursday add 4 days to
N25. hence my formula

IF(OR(R25="Thursday",N25+4<J25,"Early",IF(N25+4=J2 5,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Ea rly",IF(N25+1=J25,"OnTime","Late")))))

Which results in error.

Thanks
Elaine





"winnie123" wrote:

Hello,

I have the following formula which is currently resulting in an error. It is
checking the shiped date in column N and adding 1 day on against the agreed
despatch date.

IF(OR(R25="Thursday",N25+4<J25,"Early",IF(N25+4=J2 5,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Ea rly",IF(N25+1=J25,"OnTime","Late")))))


Basically I want to check if Cell R25 has a Thursday date and if so add 4
days onto N25, also if N25 does not have any value to return as blank.

Any suggestions please?

Thanks
Winnie

Value error with this formula
 

Sorry your new formula is confusing.. However I have got two suggestions

1. Is R25 a text value or date? If R25 is a date you can use the WEEKDAY()
function which returns the day of the week. 5 would be Thursday..
=IF(AND(WEEKDAY(R25)=5,N25<""),N25+4,"")

2. Your new formula has got a syntax problem.. Where do the OR condition
stop. I dont see the closing braces for the OR condition.
=IF(N25="","",IF(OR(R25="Thursday",N25+4<J25),"Ear ly",IF(N25+4=J25,"OnTime","Late")))

If you can respond with the sample values in the cells and your expected
results for the sample values; it would be easy to understand...

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


"winnie123" wrote:

Hello,

I have the following formula which is currently resulting in an error. It is
checking the shiped date in column N and adding 1 day on against the agreed
despatch date.

IF(OR(R25="Thursday",N25+4<J25,"Early",IF(N25+4=J2 5,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Ea rly",IF(N25+1=J25,"OnTime","Late")))))


Basically I want to check if Cell R25 has a Thursday date and if so add 4
days onto N25, also if N25 does not have any value to return as blank.

Any suggestions please?

Thanks
Winnie

Value error with this formula
 

Sorry, I am not explaining myself very well today.

What I am trying to do is if the despatch date in col N is a Thursday ( I
created col R = N with format to dddd) day then instead of adding 1 day onto
N25 , I want 4 days adding.Only when we ship on a Thursday.

Col J is the agreed date.

=IF(N25="","",IF(N25+1<J25,"Early",IF(N25+1=J25,"O nTime","Late")))

This at the moment says if the despatch is blank then return blank otherwise
add 1 day and if that is less than or equal to the Agreed Date col J return
"Early, if it equals J then "OnTime" otherwise "Late"

Thanks

"Jacob Skaria" wrote:

Sorry your new formula is confusing.. However I have got two suggestions

1. Is R25 a text value or date? If R25 is a date you can use the WEEKDAY()
function which returns the day of the week. 5 would be Thursday..
=IF(AND(WEEKDAY(R25)=5,N25<""),N25+4,"")

2. Your new formula has got a syntax problem.. Where do the OR condition
stop. I dont see the closing braces for the OR condition.
=IF(N25="","",IF(OR(R25="Thursday",N25+4<J25),"Ear ly",IF(N25+4=J25,"OnTime","Late")))

If you can respond with the sample values in the cells and your expected
results for the sample values; it would be easy to understand...

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


"winnie123" wrote:

Hello,

I have the following formula which is currently resulting in an error. It is
checking the shiped date in column N and adding 1 day on against the agreed
despatch date.

IF(OR(R25="Thursday",N25+4<J25,"Early",IF(N25+4=J2 5,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Ea rly",IF(N25+1=J25,"OnTime","Late")))))


Basically I want to check if Cell R25 has a Thursday date and if so add 4
days onto N25, also if N25 does not have any value to return as blank.

Any suggestions please?

Thanks
Winnie

Value error with this formula
 

Since your formatting cells to dddd, it looks like you're using true dates.
Combining the WEEKDAY function with what you have already:

=IF(N25="","",IF(N25+IF(WEEKDAY(R25)=5,4,1)<J25,"E arly",IF(N25+IF(WEEKDAY(R25)=5,4,1)=J25,"OnTime"," Late")))

Note that even though R25 my be displaying "Thursday", its value is actually
numerical, and thus using an check of R25="Thursday" will result in false.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"winnie123" wrote:

Sorry, I am not explaining myself very well today.

What I am trying to do is if the despatch date in col N is a Thursday ( I
created col R = N with format to dddd) day then instead of adding 1 day onto
N25 , I want 4 days adding.Only when we ship on a Thursday.

Col J is the agreed date.

=IF(N25="","",IF(N25+1<J25,"Early",IF(N25+1=J25,"O nTime","Late")))

This at the moment says if the despatch is blank then return blank otherwise
add 1 day and if that is less than or equal to the Agreed Date col J return
"Early, if it equals J then "OnTime" otherwise "Late"

Thanks

"Jacob Skaria" wrote:

Sorry your new formula is confusing.. However I have got two suggestions

1. Is R25 a text value or date? If R25 is a date you can use the WEEKDAY()
function which returns the day of the week. 5 would be Thursday..
=IF(AND(WEEKDAY(R25)=5,N25<""),N25+4,"")

2. Your new formula has got a syntax problem.. Where do the OR condition
stop. I dont see the closing braces for the OR condition.
=IF(N25="","",IF(OR(R25="Thursday",N25+4<J25),"Ear ly",IF(N25+4=J25,"OnTime","Late")))

If you can respond with the sample values in the cells and your expected
results for the sample values; it would be easy to understand...

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


"winnie123" wrote:

Hello,

I have the following formula which is currently resulting in an error. It is
checking the shiped date in column N and adding 1 day on against the agreed
despatch date.

IF(OR(R25="Thursday",N25+4<J25,"Early",IF(N25+4=J2 5,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Ea rly",IF(N25+1=J25,"OnTime","Late")))))


Basically I want to check if Cell R25 has a Thursday date and if so add 4
days onto N25, also if N25 does not have any value to return as blank.

Any suggestions please?

Thanks
Winnie

Value error with this formula
 

Thanks Luke,

The formula is just what I needed.

Before your post I decided to add a new column (AA) to determin the day of
the despatch date

=IF(R2="","",IF(AND(WEEKDAY(R2)=5,N2<""),N2+4,N2+ 1))

This will add 4 days if despatched on Thursday and 1 day in shipped any
other day.

Then I changed my original formula

From
=IF(N25="","",IF(N25+1<J25,"Early",IF(N25+1=J25,"O nTime","Late")))

To
=IF(AA25="","",IF(AA25<J25,"Early",IF(AA25=J25,"On Time","Late")))


Thanks ever so much.

"Luke M" wrote:

Since your formatting cells to dddd, it looks like you're using true dates.
Combining the WEEKDAY function with what you have already:

=IF(N25="","",IF(N25+IF(WEEKDAY(R25)=5,4,1)<J25,"E arly",IF(N25+IF(WEEKDAY(R25)=5,4,1)=J25,"OnTime"," Late")))

Note that even though R25 my be displaying "Thursday", its value is actually
numerical, and thus using an check of R25="Thursday" will result in false.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"winnie123" wrote:

Sorry, I am not explaining myself very well today.

What I am trying to do is if the despatch date in col N is a Thursday ( I
created col R = N with format to dddd) day then instead of adding 1 day onto
N25 , I want 4 days adding.Only when we ship on a Thursday.

Col J is the agreed date.

=IF(N25="","",IF(N25+1<J25,"Early",IF(N25+1=J25,"O nTime","Late")))

This at the moment says if the despatch is blank then return blank otherwise
add 1 day and if that is less than or equal to the Agreed Date col J return
"Early, if it equals J then "OnTime" otherwise "Late"

Thanks

"Jacob Skaria" wrote:

Sorry your new formula is confusing.. However I have got two suggestions

1. Is R25 a text value or date? If R25 is a date you can use the WEEKDAY()
function which returns the day of the week. 5 would be Thursday..
=IF(AND(WEEKDAY(R25)=5,N25<""),N25+4,"")

2. Your new formula has got a syntax problem.. Where do the OR condition
stop. I dont see the closing braces for the OR condition.
=IF(N25="","",IF(OR(R25="Thursday",N25+4<J25),"Ear ly",IF(N25+4=J25,"OnTime","Late")))

If you can respond with the sample values in the cells and your expected
results for the sample values; it would be easy to understand...

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


"winnie123" wrote:

Hello,

I have the following formula which is currently resulting in an error. It is
checking the shiped date in column N and adding 1 day on against the agreed
despatch date.

IF(OR(R25="Thursday",N25+4<J25,"Early",IF(N25+4=J2 5,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Ea rly",IF(N25+1=J25,"OnTime","Late")))))


Basically I want to check if Cell R25 has a Thursday date and if so add 4
days onto N25, also if N25 does not have any value to return as blank.

Any suggestions please?

Thanks
Winnie