| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF ERROR Formula | Excel Worksheet Functions | |||
| Formula Error | Excel Worksheet Functions | |||
| Formula Error | Excel Worksheet Functions | |||
| How do I replace "#N/A" error, to continue my formula w/o error? | Excel Worksheet Functions | |||
| Formula error | Excel Worksheet Functions | |||
Hybrid Mode
