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.
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
