Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dates in Excel
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Dates in Excel
Resolved · High Priority · Version 2003
Reply Tue 8 Jul 2008, 16:02Delegate Christopher said...
Christopher has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Dates in Excel
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.
For upcoming training course dates see: Pricing & availability
RE: Dates in Excel
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
|