Formula - (DATE Function Help)

%3CLINGO-SUB%20id%3D%22lingo-sub-1990808%22%20slang%3D%22en-US%22%3EFormula%20-%20(DATE%20Function%20Help)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1990808%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20some%20trouble%20figuring%20out%20the%20correct%20date%20formula%20for%20the%20sheet%20attached%20in%20order%20to%20meet%20the%20specific%20criteria%20I%20want%20to%20meet.%20More%20specific%20notes%20are%20in%20the%20sheet%20attached.%20If%20there%20any%20ideas%20that%20anyone%20would%20recommend%20with%20this%20to%20improve%20my%20sheet%2C%20I%20am%20open%20to%20learning%20anything%20new%20with%20Excel%20and%2For%20constructive%20criticism.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EOperating%20System%20-%20Windows%2010%3C%2FU%3E%3C%2FP%3E%3CP%3E%3CU%3EExcel%20Version%20-%20Microsoft%20365%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1990808%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1991057%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20-%20(DATE%20Function%20Help)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1991057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572655%22%20target%3D%22_blank%22%3E%40T-Meyers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20what%20do%20you%20consider%20as%20first%20week%20of%20the%20year%20and%20which%20data%20is%20the%20basic%20in%20Q4%3AQ6%20(if%20you%20you%20take%20simultaneously%20january%202020%20and%20week%2337%20it%20has%20no%20sense).%20If%20base%20on%20year%20and%20week%23%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDATE(Q4%2C%201%2C%20-3%20%2B%207%20*%20Q6%20-%20WEEKDAY(DATE(Q4%2C%201%2C%204)%2C%202))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20the%20Sun%2C%20and%20for%20next%20just%20add%20%2B1%3C%2FP%3E%0A%3CP%3EI%20hope%20two%20Thu%20here%20is%20misprint%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20212px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F240818i74A20C8C7AC3675B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993665%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20-%20(DATE%20Function%20Help)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BHi%20again%2C%20I%20do%20appreciate%20your%20help.%20Maybe%20if%20I%20list%20what%20I%20am%20trying%20to%20accomplish%20it%20will%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20days%20of%20the%20week%20on%20this%20sheet%20are%20static%20and%20do%20not%20change.%3C%2FP%3E%3CP%3E2.%20Each%20week%20I%20print%20out%20this%20sheet%20for%20the%20employees%20I%20oversee.%3C%2FP%3E%3CP%3E3.%20As%20each%20week%20consists%20of%207%20days%20and%20might%20overlap%20with%20the%20next%2Fprevious%20month.%20Is%20there%20a%20way%20to%20match%20the%20days%20of%20the%20week%20listed%20to%20the%20actual%20current%20week%3F%20As%20an%20example%2C%20this%20week%20%2212%2F13%2F2020%20-%2012%2F19%2F2020%22.%20I%20would%20want%20the%20days%20to%20auto%20fill%20to%20match%20the%20way%20I%20have%20my%20days%20listed.%3C%2FP%3E%3CP%3E4.%20If%20step%20%233%20is%20possible%2C%20I%20want%20to%20create%20a%20way%20that%20I%20could%20select%20from%201-3%20drop%20down%20lists%20to%20change%20the%20whole%20sheet%20to%20the%20matching%20dates.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Replies

@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

image.png

 

@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.