> -----Original Message----- > From: McGlinchy, Alistair > 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)-WEEKDA Y(DATE(YEA > R(25569+A4/86400),5,1),2)+1/24<25569+A4/86400,25569+A4/86400<D ATE(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. >
A couple more small changes: AND(X,Y) can be changed to (X)*(Y) in this case 1/24*X can be changed to X/24 2/24 can be changed to 1/12, although it's not shorter 86400/24 can be changed to 3600 SO... =25569+(A4/3600+DATE(YEAR(25569+A4/86400),5,1)-WEEKDAY(DATE(YEAR(25569+A4/86 400),5,1),2)+1/24<25569+A4/86400)*(25569+A4/86400<DATE(YEAR(25569+A4/86400), 11,1)-WEEKDAY(DATE(YEAR(25569+A4/86400),11,1),2)+1/12)/24 LEGAL NOTICE Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.
