Dates in Excel

Resolved · High Priority · Version 2003

Tue 8 Jul 2008, 16:02 Delegate 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:53 Trainer 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 courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Showing all menu items

If 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.
To turn this off so that you always get everything, go to Tools / Customize / Options Tab / "Always show full menus". Make sure there is a tick in the box and you will always have every item.

View all Excel hints and tips