How to return date value based on "day of week" value in Excel

Question

How to return date value based on "day of week" value in Excel

Private PNW 0

I am designing a habit tracker for my own personal use. There are three categories in this habit tracker, and all three are based on days of the week. One category is M, T, W, Th, F, Sat, the second category is M, W, F, and the third category is T, Th, Sat. I've inserted a screenshot (personal info completely removed, just the skeletal framework exists) of the worksheet below. My goal is to Excel to return dates underneath the days of the week automatically based on whatever month I have on another worksheet (same workbook). I want to update this habit tracker every two weeks. For example, November 2, 2026 was a Monday, so that should have showed in cell M5 and so on. In December, for the "first Monday" (not the actual first Monday, but the first day) in December, I want Excel to show November 30, 2026. I don't know how to set up an automatic rollover of every two weeks in Excel, or even if that's the direction I should be going into. When I try to research how to insert two weeks, I run into the problem where it's not a rolling function like in the first week of December. I want the week to show November 30, December 1, December 2 because the Monday that month is still in November but then it switches over to December on Tuesday in the same week. I also have the same problem with trying to populate the MWF and TTHSA dates/days/weeks.

Thank you.

screenshot - Copy

0 comments

1 answer

Your answer

Answer 1

Store the selected month as a real date, calculate the appropriate starting Monday, and use spilled formulas to generate all three two-week schedules automatically.

1. Create the month selector

On the other worksheet—assumed below to be named Setup—enter the first day of the required month in B2, for example:

=DATE(2026,11,1)

Format B2 as mmmm yyyy if you want it displayed as November 2026. Keep it as a real date rather than text; Excel stores dates as numbers, allowing days to be added or subtracted reliably.^1^

The formulas below implement this rule:

Use the Monday in the week containing the first day of the selected month.
If the month starts on Sunday, use the following Monday instead.

Therefore:

November 2026 starts on Monday, November 2, 2026.
December 2026 starts on Monday, November 30, 2026.

2. Daily schedule: M, T, W, Th, F, Sat

Enter this in the first date cell under the first Monday—for example, M5:

=LET(
 first,DATE(YEAR(Setup!$B$2),MONTH(Setup!$B$2),1),
 wd,WEEKDAY(first,2),
 monday,first-wd+1+7*(wd=7),
 n,SEQUENCE(1,12,0),
 monday+n+INT(n/6)
)

It spills across 12 cells and returns:

Week 1: Monday through Saturday
Week 2: Monday through Saturday

WEEKDAY(date,2) numbers Monday as 1 through Sunday as 7.^2^ SEQUENCE creates the horizontal array, and its result spills into adjacent cells automatically.^3^

3. Monday, Wednesday, Friday schedule

In the first date cell beneath the first M in that section, enter:

=LET(
 first,DATE(YEAR(Setup!$B$2),MONTH(Setup!$B$2),1),
 wd,WEEKDAY(first,2),
 monday,first-wd+1+7*(wd=7),
 n,SEQUENCE(1,6,0),
 monday+2*MOD(n,3)+7*INT(n/3)
)

This spills six dates:

Monday, Wednesday, Friday of week 1
Monday, Wednesday, Friday of week 2

For November 2026, the result is:

11/2, 11/4, 11/6, 11/9, 11/11, 11/13

4. Tuesday, Thursday, Saturday schedule

In the first date cell beneath the first T in that section, enter:

=LET(
 first,DATE(YEAR(Setup!$B$2),MONTH(Setup!$B$2),1),
 wd,WEEKDAY(first,2),
 monday,first-wd+1+7*(wd=7),
 n,SEQUENCE(1,6,0),
 monday+1+2*MOD(n,3)+7*INT(n/3)
)

For November 2026, this returns:

11/3, 11/5, 11/7, 11/10, 11/12, 11/14

5. Format and use the results

Select all cells containing the generated dates.
Press Ctrl+1.
Select Custom.
Use:
- d to display only the day number, or
- m/d to display month and day.
Leave the cells to the right of each formula empty so the results can spill. Otherwise, Excel returns #SPILL!.

Changing only Setup!B2 to another month will recalculate all three sections, including dates belonging to the preceding month, such as November 30 in the December 2026 schedule.

References