Date reformatting question
Hi again,
Am Tue, 6 Nov 2012 09:13:36 +0100 schrieb Claus Busch:
Your date and time in A1 then try:
=LEFT(A1,19)-TIME(9-(AND(--LEFT(A1,10)=DATE(LEFT(A1,4),3,1)+13-(WEEKDAY(DATE(LEFT(A1,4),3,1)-1)-1),--LEFT(A1,10)<=DATE(LEFT(A1,4),10,31)-(WEEKDAY(DATE(LEFT(A1,4),10,31))-1)+7)),,)
^ ^^^^^^
sorry, i have a typo in the formula. Try:
=LEFT(A1,19)-TIME(8-(AND(--LEFT(A1,10)=DATE(LEFT(A1,4),3,1)+13-(WEEKDAY(DATE(LEFT(A1,4),3,1)-1)-1),--LEFT(A1,10)<=DATE(LEFT(A1,4),10,31)-(WEEKDAY(DATE(LEFT(A1,4),10,31))-1)+7)),,)
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|