Hello,



In my spreadsheet (in cell A4), I have the formula =TODAY(). In cell A3, the formula is: =A4-1. The formula in cell A3 works well Tue-Fri, but when I come into work on Monday, I need cell A3 to display a Friday date and not a Sunday date. Is there any way to modify this formula so it skips over weekends?

Your help is greatly appreciated,
Karen

For a dynamic formula that updates daily, you could use:
For a formula that references the date in another cell, you could use:

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Or in A3 use (assumed the worksheet is never used at weekends, and you have to cope with state holidays otherwise)
In case you need to take holidays also into account, you need a hidden calendar table with state holidays marked, and with a calculated column which returns the number of days to previous workday in case of holiday or weekend (p.e. for my country, this may be up to 5 days if I remember correctly), and 0 in case of workday. The formula in A3 will calculate the previous workday based on this hidden table.

=TODAY()-1-(MOD(TODAY(),7)=2)*2

Thank you so much for your help. If you have a chance, would you please explain this formula in layman's terms?

Your help is greatly appreciated.
Thank you,
Karen

Thank you so much for your help. If you have a chance, would you please explain this formula in layman's terms?

Also, I do need to deal with holidays. I'm not sure how to set up that hidden calendar.

Your help is greatly appreciated.
Thank you,
Karen

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
The CHOOSE() returns from values at 2nd to last parameters, the one with order number equal to value of 1st parameter

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

In the attached, formula in cell B4:
=WORKDAY(A4,-1,Holidays)
You could have more control with
=WORKDAY.INTL(A4,-1,1,Holidays)
in cell B4 if WORKDAY.INTL is available to you in your version of Excel.
Holidays is the name of a table containing a list of holiday dates.

For demonstration purposes, in the attached:
The date in cell A4 is not =TODAY() but fixed to today's date (22/Nov/2024) but this will still work if you do use =TODAY()

Also, I've added a few more dates below cell A4 and used the same formulae in columns B & C. I've added conditional formatting to column A to highlight holiday dates and weekends so that you can confirm the formulae give the correct results.

2024-11-22_162713.jpg

Attached Files

msofficeforums53020.xlsx (11.3 KB, 3 views)

Dates in Excel are stored as numbers, going back to 1 Jan 1901, and incrementing by 1 for each day.

When we divide a date by 7, the remainder tells us what day of the week it is. If the remainder is:
0 - the day is a Saturday
1 - the day is a Sunday
2 - the day is a Monday
3 - the day is a Tuesday
4 - the day is a Wednesday
5 - the day is a Thursday
6 - the day is a Friday

The MOD function returns the remainder of a number and its divisor. Hence, if the remainder returned by MOD function of a date divided by 7 is 2, the date is a Monday.

So:
TODAY()-1
returns yesterday
MOD(TODAY(),7)=2
is a True/False test for Monday and, if TRUE
-(MOD(TODAY(),7)=2)*2
deduct another 2 days from today.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you so much for your detailed reply! Your explanation is incredibly helpful, and I truly appreciate the time and effort you put into this. People on this site are amazing!
Happy holidays!