98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dates in Excel
Dates in Excel
Resolved · High Priority · Version 2003
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.
Thu 10 Jul 2008, 10:53Trainer Katie said...
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
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. |
Excel tip:Showing all menu itemsIf you go into a pull down menu you usally find that you get a selection of items(this is the default) or sometimes everything. If you only get a selectiopn you have to go to the double arrows at the bottom of the menu and click it to get all of the hidden items. |
