Excel 2007 Query

shaz77_2

somethingcorporate wrote: »

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

Weekends are still a problem, if I input Friday 14/09/12 at 14:00 as the arrival time and Monday 17/09/12 at 14:00 as the despatch time it returns a failed when this should be a pass.

somethingcorporate

I didn't realise that should be a pass, I assumed if it was Friday then it would need to be Saturday to pass. I can build it in.

I will look at it again tonight, quite a few little quirks that make this complex!

shaz77_2

somethingcorporate wrote: »

I didn't realise that should be a pass, I assumed if it was Friday then it would need to be Saturday to pass. I can build it in.

I will look at it again tonight, quite a few little quirks that make this complex!

Thanks for that, everytime I think I have it solved another problem arises but basically anything that arrives after 13:00 on a Friday should be a pass if despatched on the following Monday.

somethingcorporate

Yep, that is not too complicated (just something that I had not picked up from everyone elses' code in the thread).

Stompa

shaz77 wrote: »

Thanks for that, everytime I think I have it solved another problem arises but basically anything that arrives after 13:00 on a Friday should be a pass if despatched on the following Monday.

I'm still at a loss to know what's wrong with my earlier simple solution, it does precisely that! This really is a trivial problem, that should only take a few minutes to code.

somethingcorporate

Stompa wrote: »

I'm still at a loss to know what's wrong with my earlier simple solution, it does precisely that! This really is a trivial problem, that should only take a few minutes to code.

I tried this solution, appears to do the trick.

Not sure why the OP missed it first time around, it's very elegant compared to mine (which only partially works). :T

Stompa

somethingcorporate wrote: »

Not sure why the OP missed it first time around, it's very elegant compared to mine (which only partially works). :T

I don't know about elegant, but given that Excel has functions to manipulate dates and times it seems to make sense to use them!

shaz77_2

Just testing the following code out at a different PC (this has Excel 2010 on it) and the following code brings up the #NAME? error message.

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

Stompa

shaz77 wrote: »

Just testing the following code out at a different PC (this has Excel 2010 on it) and the following code brings up the #NAME? error message.

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

Yes, you need to substitute 'dayin' and 'dayout' with the actual cells containing your dates, for example:

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

somethingcorporate

Stompa wrote: »

I don't know about elegant, but given that Excel has functions to manipulate dates and times it seems to make sense to use them!

Yep, very clever