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

Private PNW 0 Reputation points
2026-11-08T06:48:29.1333333+00:00

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

Microsoft 365 and Office | Excel | For home | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Private PNW 0 Reputation points
    2026-11-08T18:47:20.7433333+00:00

    Hello,

    The first two formulas (daily and MWF) worked, but the third formula (for TTHSA) didn't work. Excel keeps telling me that I'm trying to return too many arguments. The AI answer was partially successful and partially not successful.

    I have a follow-up question. How do I get Excel to update the dates every two weeks? For instance, I want to do my habit tracker every two weeks. I can do the first two weeks of each month, but what about the middle two weeks of each month and the last two weeks of each month? I'm not sure if Excel has a rolling feature where I can just hit a few keys and the dates will roll over to the next two weeks, then when I hit a few more keys, the dates will roll over to the next-to-next two weeks.

    Thank you.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.