Formula - (DATE Function Help)

[T-Meyers]

Hi everyone,

I am having some trouble figuring out the correct date formula for the sheet attached in order to meet the specific criteria I want to meet. More specific notes are in the sheet attached. If there any ideas that anyone would recommend with this to improve my sheet, I am open to learning anything new with Excel and/or constructive criticism.

Any help is greatly appreciated.

Operating System - Windows 10

Excel Version - Microsoft 365

[#2]

@T-Meyers 

It depends on what do you consider as first week of the year and which data is the basic in Q4:Q6 (if you you take simultaneously january 2020 and week#37 it has no sense). If base on year and week# that could be like

=DATE(Q4, 1, -3 + 7 * Q6 - WEEKDAY(DATE(Q4, 1, 4), 2))

for the Sun, and for next just add +1

I hope two Thu here is misprint

[#3]

@Sergei Baklan Hi again, I do appreciate your help. Maybe if I list what I am trying to accomplish it will help.

1. The days of the week on this sheet are static and do not change.

2. Each week I print out this sheet for the employees I oversee.

3. As each week consists of 7 days and might overlap with the next/previous month. Is there a way to match the days of the week listed to the actual current week? As an example, this week "12/13/2020 - 12/19/2020". I would want the days to auto fill to match the way I have my days listed.

4. If step #3 is possible, I want to create a way that I could select from 1-3 drop down lists to change the whole sheet to the matching dates.