On Tue, 26 Oct 2004, McGlinchy, Alistair wrote: > > 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. >
I've been looking into this a bit more. If you want the time in GMT, it's easy: =25569+A2/86400 and apply whatever format you want to the cell. I don't know if you can have the time in other timezones though. Excel doesn't seem to know about them, unless I'm missing something. I set my clock to Pacific time, and NOW() changed to 38286.29 (i.e. 29% of the way through today), and 1/1/1970 00:00:00 was still 25569.00. -- Stephen Turner, Cambridge, UK http://homepage.ntlworld.com/adelie/stephen/ "Low Priced Cambridge Clare College. Big selection at eBay UK!" (Ad after Google search for Clare College Cambridge)
