Quote:
Originally Posted by Karen615
Thank you so much for your help. If you have a chance, would you please explain this formula in layman's terms?
|
The formula returns the date of previous Friday (TODAY-3), when the current date is Monday, i.e. the weekday number of current date is 1 (I used WEEKDAY() with 2nd argument 2 to get weekdays numbered from 1 = Monday to 7 = Sunday).
For other workdays (from Tuesday to Friday, with weekday numbers from 2 to 5), the formula returns the date of previous workday (TODAY -1).
As I declared in my precvious post, the formula in not meant to use at weekends.
In case you want this formula to work when the workbook is opened at weekends (but ignoring holidays) too, you have to use instead
Code:
=TODAY() - CHOOSE(WEEKDAY(TODAY(),2),3,1,1,1,1,1,2)
The CHOOSE() returns from values at 2nd to last parameters, the one with order number equal to value of 1st parameter
Quote:
|
Also, I do need to deal with holidays. I'm not sure how to set up that hidden calendar.
|
On separate sheet, create a table with header for column A (e.g. in cell A1) something like "Date". Into cell A2 enter the start date (e.g. January 1st of current year), into cell A2, enter the next date (e.g. January 2nd of current year), select both dates, and drag down (from right lower edge) to fill the column with as much dates you think you will need;
Add another column (e.g. column B with header "Weekday"), and fill it with weekday numbers using WEEKDAY() formula;
Add another column (e.g. column C with header "Holiday"), and fill it with number 1 for every holiday in daterange of your table. When the date isn't a holiday, leave it empty, or enter 0;
Add another column (e.g. column D with header "PreviousWD"). Based on info in columns B:C, fill this column with 0 when the date is workday and it is not a holiday. Otherwise enter, how many days back was last workday (1, or 2, or ... days). It is possible to create a formula for this column, but I'm afraid this will be too complex for you, and I don't have access to Excel currently, and even having it, the creating such formula will take hours for me too.
Now either define this calendar table as Table with name like tCalendar, or select the whole datarange of created table, and define it as Name like nCalendar (I'll continue with defined Name below).
On your original sheet, into cell A3 enter the formula like
Code:
=(TODAY()-1) - VLOOKUP((TODAY()-1),nCalendar,4,0)
The 3rd parameter of VLOOKUP() in formula indicates the 4th column of range nCalendar - i.e. the VLOOKUP() returns the matching number in column PreviousWD.
After checking the formula for previous workday working, you can hide the sheet with calendar table you created.