Function/Formula to Show Weekdays Only

zero269 · Jan 30, 2023

Hello,

I'm looking for a better way to fill down the Weekdays without using the Fill Down > Fill Weekdays option as it messes up my formatting.
Currently I enter a Starting Date, and the cells below =Today()+1 to FRI.
On Mondays, I have to use =Today()+3 to skip the Weekends.
This creates an Inconsistent Error message that I'm tired of looking at.

I considered the WEEKDAY function, but it doesn't seem to work for me considering I'm using a start date that needs to be entered manually as I'm only tracking a 3 week period at a time in the same space.

WEEKDAY function - Microsoft Support

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

support.microsoft.com

Reading Tracker.xlsm

B

C

22

Scheduling

23

Day

Test Date

24

MON

30-Jan

25

26

27

28

29

30

31

32

33

34

35

36

37

38

Dashboard

Cell Formulas
Range	Formula
B24:B38	B24	=UPPER(TEXT($C24,"ddd"))
C25:C28,C30:C33,C35:C38	C25	=$C24+1
C29,C34	C29	=$C28+3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C24:P39	Expression	=AND($C24=TODAY())	text	NO
C24:P39	Expression	=AND($C24=TODAY()+1)	text	NO

jasonb75 · Jan 30, 2023

You need WORKDAY, not WEEKDAY for what you're doing.

Excel Formula:

=WORKDAY(C24,1)

zero269 · Jan 31, 2023

jasonb75 said:
You need WORKDAY, not WEEKDAY for what you're doing.

Hi Jason,

Thanks for steering me in the right direction. I almost gave up on using that function just based on Microsoft's description of its use...

WORKDAY function - Microsoft Support

Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

support.microsoft.com

... until I saw this example that shows it being used to meet my needs.

WORKDAY Function

The WORKDAY function is categorized under Excel Date and Time functions. It will provide us with a date that is N working days in the future or in the past.

corporatefinanceinstitute.com

So, I put it to use in my Workbook and it works perfectly!

Reading Tracker.xlsm