Dec 15 2020 12:30 PM
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
Dec 15 2020 01:39 PM
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
Dec 16 2020 06:48 AM
@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.