Excel 2007 Query

rmg1

Apologies, I didn't realise you could do weekend despatches.
Try this instead:-

=IF(TEXT(A2,"dd/mm/yyyy")=TEXT(B2,"dd/mm/yyyy"),"Pass",IF(TEXT(A2,"dddd")="Friday",IF(AND(H OUR(A2)>=13,TEXT(B2,"dd/mm/yyyy")<=TEXT(A2+3,"dd/mm/yyyy")),"Pass","Fail"),IF(AND(HOUR(A2)>=13,TEXT(B2,"dd/mm/yyyy")=TEXT(A2+1,"dd/mm/yyyy")),"Pass","Fail")))

shaz77_2

rmg1 wrote: »

Apologies, I didn't realise you could do weekend despatches.
Try this instead:-

=IF(TEXT(A2,"dd/mm/yyyy")=TEXT(B2,"dd/mm/yyyy"),"Pass",IF(TEXT(A2,"dddd")="Friday",IF(AND(H OUR(A2)>=13,TEXT(B2,"dd/mm/yyyy")<=TEXT(A2+3,"dd/mm/yyyy")),"Pass","Fail"),IF(AND(HOUR(A2)>=13,TEXT(B2,"dd/mm/yyyy")=TEXT(A2+1,"dd/mm/yyyy")),"Pass","Fail")))

The following error message is appearing "#NAME?".

shaz77_2

Stompa wrote: »

Does the one I gave previously not work for you? And, if not, in what circumstances does it fail?

It works fine up until the part for weekends is added.

shaz77_2

I'll have another go at trying to resolve this one - it's very tricky.

Stompa

shaz77 wrote: »

It works fine up until the part for weekends is added.

Unfortunately I have no idea what you mean by "up until the part for weekends is added", it should work as it stands without adding anything. If you could provide more detail I'm happy enough to take a look.

somethingcorporate

shaz77 wrote: »

The following error message is appearing "#NAME?".

If you look at the quote there is a space in one of the words "H OUR". If you take this out it should resolve the name issue.

shaz77_2

somethingcorporate wrote: »

If you look at the quote there is a space in one of the words "H OUR". If you take this out it should resolve the name issue.

That part of the problem is fixed, however if I put down the arrival date as 15/09/12 14:00 and the depatch date as 17/09/12 12:00 it returns a fail, however this should not be the case as the 15th was a Saturday.

somethingcorporate

Try this in C2:

[FONT=Calibri, sans-serif]=IF(ROUNDDOWN(IF(WEEKDAY(A2)=7,IF(HOUR(A2)<13,A2,(A2)+2),IF(HOUR(A2)<13,A2,(A2)+1)),0)-ROUNDDOWN(B2,0)=0,"Pass","Fail")

You may need to change the < to <= depending on how you treat the 13:00 deadline.

This looks at whether the weekday in question was 7 (Friday) and adds 2 days to the required by date for comparison.

The slight untidy thing about this formula is if you then dispatch it on a Sunday it would fail. I can tidy this up further if you need me to for peripheral logical like that. Just want to check the above works ok first.

FYI the rounddown element of the formula takes out the time from the cells, since we don't care about this for when it is dispatched (only for when it arrives).
[/FONT]

shaz77_2

somethingcorporate wrote: »

Try this in C2:

[FONT=Calibri, sans-serif]=IF(ROUNDDOWN(IF(WEEKDAY(A2)=7,IF(HOUR(A2)<13,A2,(A2)+2),IF(HOUR(A2)<13,A2,(A2)+1)),0)-ROUNDDOWN(B2,0)=0,"Pass","Fail")[/FONT]

[FONT=Calibri, sans-serif]You may need to change the < to <= depending on how you treat the 13:00 deadline.[/FONT]

[FONT=Calibri, sans-serif]This looks at whether the weekday in question was 7 (Friday) and adds 2 days to the required by date for comparison.[/FONT]

[FONT=Calibri, sans-serif]The slight untidy thing about this formula is if you then dispatch it on a Sunday it would fail. I can tidy this up further if you need me to for peripheral logical like that. Just want to check the above works ok first.[/FONT]

[FONT=Calibri, sans-serif]FYI the rounddown element of the formula takes out the time from the cells, since we don't care about this for when it is dispatched (only for when it arrives).[/FONT]

What would be the solution to despatching on a Sunday as this would need to be a pass in ther rare event of this actually happening.

somethingcorporate

shaz77 wrote: »

What would be the solution to despatching on a Sunday as this would need to be a pass in ther rare event of this actually happening.

I will give it some thought. Have you tried the rest of the code?