Excel 2007 Query

rmg1

Try this:
=IF(TEXT(A2,"dd/mm/yyyy")=TEXT(B2,"dd/mm/yyyy"),"Pass",IF(TEXT(A2,"dddd")="Friday",IF(AND(HOUR(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")))

Similar to the first one but now takes the arrival day into account.
Seems to work at a quick check.

paddyrg

Just to reassure you OP, business rules around dates are responsible for about half of the work in IT - it is always more complicated than it sounds! For instance when people want a stock list and a sales list and try to combine them, with some sort of tests for date ranges... Well it throws up about a zillion things you're touching the edges of! So be assured, it's not you being dumb, but surprisingly complicated!

Stompa

It seems to me that something like:

=IF(dayout<1+WORKDAY(dayin,1-(HOUR(dayin)<13)),"Pass","Fail")

should do the trick.

shaz77_2

rmg1 wrote: »

Try this:
=IF(TEXT(A2,"dd/mm/yyyy")=TEXT(B2,"dd/mm/yyyy"),"Pass",IF(TEXT(A2,"dddd")="Friday",IF(AND(HOUR(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")))

Similar to the first one but now takes the arrival day into account.
Seems to work at a quick check.

Thanks again, I've tried your code above but it return a parenthesis error which I can't resolve, do you know what the problem could be? I've removed all additional code that I had within the cell and it still doesn't seem to work.

shaz77_2

shaz77 wrote: »

Thanks again, I've tried your code above but it return a parenthesis error which I can't resolve, do you know what the problem could be? I've removed all additional code that I had within the cell and it still doesn't seem to work.

Thought I'd bump this up again as it's been bugging me all day.

rmg1

shaz77 wrote: »

Thanks again, I've tried your code above but it return a parenthesis error which I can't resolve, do you know what the problem could be? I've removed all additional code that I had within the cell and it still doesn't seem to work.

I've just copied the formula direectly into a cell and it works fine for me.
Are you sure you grabbed all of the formula?

shaz77_2

It doesn't seem to recognise Fridays, at present if I put in the arrival date as 07/09/2012 14:00 and the despatch date as 08/09/2012 12:00 it is returning fail when this should be pass.

I've copied across the code 3 times now to no avail.

Stompa

shaz77 wrote: »

It doesn't seem to recognise Fridays, at present if I put in the arrival date as 07/09/2012 14:00 and the despatch date as 08/09/2012 12:00 it is returning fail when this should be pass.

I've copied across the code 3 times now to no avail.

I may be wrong, but I don't think that formula accommodates despatch dates that happen to be a Saturday or Sunday. I'd go with the simpler:

=IF(B2<1+WORKDAY(A2,1-(HOUR(A2)<13)),"Pass","Fail")

which I think should work in all circumstances.

shaz77_2

I'm still not getting anywhere with these formulas, does anyone know how to combine the formulas to allow for weekends etc?

Stompa

shaz77 wrote: »

I'm still not getting anywhere with these formulas, does anyone know how to combine the formulas to allow for weekends etc?

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