Dates in Excel

Resolved · High Priority · Version 2003

Dates in Excel

Christopher has attended:
Excel Advanced course
Excel VBA course

by - delegate Christopher [3 posts] (2008 Jul 8 Tue, 16:02) Reply

Hi

I have a s/s that looks at the price of stocks it compares today against the previous day to find the % up or down. On Monday when I come to work the s/s points to Sunday therefore I cannot pull correct data in. The question is how can I write a formula to day if the date = a weekend default to the Friday before? Thanks in advance.

Version	Date	Location	Places available	Book	Next place rate:
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2008 Nov 25 Tue	Bloomsbury	0	FULL
2003	2008 Nov 27 Thu	Southwark	3	book now	£210	£215
2007	2008 Nov 28 Fri	Bloomsbury	0	FULL
2003	2008 Dec 2 Tue	Bloomsbury	3	book now	£210	£215
2003	2008 Dec 4 Thu	Bayswater	6	book now	£195	£205
2007	2008 Dec 8 Mon	Bayswater	5	book now	£212	£224
Full Schedule: See all 113 Excel Intermediate course dates. Bookings currently available until 25th November 2009.

RE: Dates in Excel

by - trainer Katie gold contributer [214 posts] (2008 Jul 10 Thu, 10:53) Reply

Hi Christopher,

Thank you for your question.

You will need to use a nested IF to filter out the weekend dates. Also, to obtain the weekday, you need the function WEEKDAY.

If the date value is in cell A1, this is an example how you can move weekends into previous Friday:
=IF(WEEKDAY(A1,2)=6,A1-1,IF(WEEKDAY(A1,2)=7,A1-2,A1))

Explaination:
The 1st IF:
WEEKDAY(A1,2)=6 - tests if date falls on Saturday
A1-1 - moves 1 day backward to previous Friday

The 2nd IF:
WEEKDAY(A1,2)=7 - tests if date falls on Sunday
A1-2 - moves 2 days backward to previous Friday

If the weekday is neither 6 nor 7, it returns the date value on cell A1.

Hope this helps.

Kind regards,

Katie Woo
Microsoft Certified Trainer