Value error with this formula

W

winnie123

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=J25,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Early",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
 
W

winnie123

Sorry,

The original formula is which works

=IF(N25="","",IF(N25+1<J25,"Early",IF(N25+1=J25,"OnTime","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=J25,"OnTime","Late")),IF(N25="","",(IF(N25+1<J25,"Early",IF(N25+1=J25,"OnTime","Late")))))

Which results in error.

Thanks
Elaine
 
J

Jacob Skaria

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),"Early",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
 
W

winnie123

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,"OnTime","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
 
L

Luke M

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,"Early",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.
 
W

winnie123

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,"OnTime","Late")))

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


Thanks ever so much.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top