From: Stephen Turner: > On Tue, 26 Oct 2004, McGlinchy, Alistair wrote: > > Here's a horrid 297 Byte solution:
> I don't know much about Excel, but I think DATE(1970,1,1) can > be spelled 25569. If I'm right, that would save you at least > 63 strokes. > I feel suitably humbled. Thanks to you and Joe for some significant improvements. =25569+A4/86400+1/24*AND(DATE(YEAR(25569+A4/86400),5,1)-WEEKDAY(DATE(YEA R(25569+A4/86400),5,1),2)+1/24<25569+A4/86400,25569+A4/86400<DATE(YEAR(2 5569+A4/86400),11,1)-WEEKDAY(DATE(YEAR(25569+A4/86400),11,1),2)+2/24) With only 214 characters, the formula is only two lines on my screen so I can see my column headings (but not Excel's column head. Yippee. Alistair ----------------------------------------------------------------------- Registered Office: Marks and Spencer plc Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. The registered office of Marks and Spencer Financial Services PLC, Marks and Spencer Unit Trust Management Limited, Marks and Spencer Life Assurance Limited and Marks and Spencer Savings and Investments Limited is Kings Meadow, Chester, CH99 9FB. These firms are authorised and regulated by the Financial Services Authority.
