SOLVED

I need a help with a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1975817%22%20slang%3D%22en-US%22%3EI%20need%20a%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1975817%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20want%20C4%3AF4%20to%20be%20automatically%20calculated%20based%20on%20the%20week%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-12-10%20at%2021.13.51.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F239548i9D1AE733E058391F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-12-10%20at%2021.13.51.png%22%20alt%3D%22Screen%20Shot%202020-12-10%20at%2021.13.51.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFor%20example%2C%20it%20needs%20to%20be%20shipped%20out%20on%2011%2F20%20to%20arrive%20in%20week%201%20of%20December.%3C%2FP%3E%3CP%3E11%2F27%20for%20week%202%2C%2012%2F4%20for%20week%203%20of%20December%2C%20and%20so%20on.%3C%2FP%3E%3CP%3EI%20think%20I%20should%20input%20Cell%20C3%20as%202020%2F12%2F01%20and%20display%20it%20as%20week1%2C%20but%20I%20can't%20really%20figure%20it%20out.%3C%2FP%3E%3CP%3EI'm%20not%20sure%20if%20my%20explanation%20makes%20sense...but%20please%20help%20if%20there%20is%20a%20way%20that%20works%20out!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1975817%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1975864%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1975864%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871690%22%20target%3D%22_blank%22%3E%40Colinahn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20COULD%20NT%20RELLY%20UNDERSTOOD%2C%20BUT%20S%20I%20UNDESTAND%2C%20THE%20BELOW%20CAN%20HELP%20YOU%2C%20TRY...!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sameer_Kuppanath_Sulta_0-1607604014602.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F239550i80584EA59A2D8E38%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Sameer_Kuppanath_Sulta_0-1607604014602.png%22%20alt%3D%22Sameer_Kuppanath_Sulta_0-1607604014602.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1975974%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1975974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sulta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20calculate%20B2%2C%20not%20B1.%3C%2FP%3E%3CP%3Eon%20Cell%20B1%2C%20I%20can%20just%20put%20week1%2C%20but%20I%20thought%20it%20needed%20to%20be%20shown%20as%20the%20date%20in%20order%20to%20calculate%20Cell%20B2.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1976780%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1976780%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871690%22%20target%3D%22_blank%22%3E%40Colinahn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%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%20276px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F239608iE7AE53665C3AFA9A%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%3Eif%20in%20C1%20first%20date%20of%20the%20month%2C%20and%20in%20D1%20delivery%20time%20(11%20days)%2C%20when%3C%2FP%3E%0A%3CP%3E-%20in%20D1%20just%20%3DC1%2C%20apply%20custom%20%3CSTRONG%3Emmmm%3C%2FSTRONG%3E%20format%20and%20align%20C2%3AF2%20with%20Center%20Across%20Selection%3C%2FP%3E%0A%3CP%3E-%20in%20C3%20is%201%2C%20in%20D3%20%3DC3%2B1%2C%20etc%20and%20apply%26nbsp%3B%20custom%20number%20format%20%3CSTRONG%3E%22week%20%220%3C%2FSTRONG%3E%20to%20C3%3AF3%3C%2FP%3E%0A%3CP%3E-%20in%20C4%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%24C%241%2B(C3-1)*7-11%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20to%20the%20right%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I want C4:F4 to be automatically calculated based on the week number.

 

Screen Shot 2020-12-10 at 21.13.51.png

For example, it needs to be shipped out on 11/20 to arrive in week 1 of December.

11/27 for week 2, 12/4 for week 3 of December, and so on.

I think I should input Cell C3 as 2020/12/01 and display it as week1, but I can't really figure it out.

I'm not sure if my explanation makes sense...but please help if there is a way that works out!

 

3 Replies

HI @Colinahn 

I COULD NT RELLY UNDERSTOOD, BUT S I UNDESTAND, THE BELOW CAN HELP YOU, TRY...!

 

Sameer_Kuppanath_Sulta_0-1607604014602.png

 

@Sameer_Kuppanath_Sultan 

Hi, 

I need to calculate B2, not B1.

on Cell B1, I can just put week1, but I thought it needed to be shown as the date in order to calculate Cell B2. 

best response confirmed by Colinahn (Occasional Contributor)
Solution

@Colinahn 

As variant

image.png

if in C1 first date of the month, and in D1 delivery time (11 days), when

- in D1 just =C1, apply custom mmmm format and align C2:F2 with Center Across Selection

- in C3 is 1, in D3 =C3+1, etc and apply  custom number format "week "0 to C3:F3

- in C4 

=$C$1+(C3-1)*7-11

and drag it to the right