Function for specific weekly dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2404149%22%20slang%3D%22en-US%22%3EFunction%20for%20specific%20weekly%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2404149%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20a%20column%20that%20is%20dated%20as%20such%3A%3C%2FP%3E%3CP%3EJanuary%2C%20Monday%204%20-%20Sunday%2010%3C%2FP%3E%3CP%3EJanuary%2C%20Monday%2011%20-%20Sunday%2017%3C%2FP%3E%3CP%3Ewhere%20only%20the%20weeks%20start%20at%20Monday.%20I%20think%20this%20has%20something%20to%20do%20with%20the%20IF%20function%3F%20But%20I'm%20a%20complete%20beginner%20and%20do%20not%20understand%20that%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20have%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTEXT(A6%2C%20%22mmmm%2C%20%22)%26amp%3BTEXT(H6%2C%22mmm%20d%22)%26amp%3BIF(H6%26lt%3B%26gt%3B%22%22%2C%22%20-%20%22%26amp%3BTEXT(I6%2C%22mmm%20d%22)%2C%22%22)%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20A6%20is%20the%20full%20date%3A%20Monday%2C%20January%204%2C%202021%2C%20H6%20is%20the%20week%20and%20date%3A%20Monday%204%2C%20and%20I6%20is%20the%20week%20and%20date%3A%20Sunday%2010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20the%20output%20for%20what%20I%20currently%20have%20is%3A%3C%2FP%3E%3CP%3EJanuary%2C%20Monday%204%20-%20Sunday%2010%3CBR%20%2F%3EJanuary%2C%20Tuesday%205%20-%20Monday%2011.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20not%20what%20I'm%20looking%20for.%20Instead%2C%20I%20need%20a%20column%20of%20dates%20only%20starting%20on%20Monday.%20Is%20someone%20able%20to%20draft%20a%20formula%20for%20this%3F%20And%20if%20need%20for%20explanation%2C%20please%20explain%20it%20for%20beginners.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2404149%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2404299%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20for%20specific%20weekly%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2404299%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1068389%22%20target%3D%22_blank%22%3E%40HannahClaire%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20in%20H6%20start%20day%20when%20in%20A6%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%20378px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285335i84E90E38E327712E%2Fimage-size%2Flarge%3Fv%3Dv2%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%3Eformula%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXT(%24H%246%20%2B%20(ROW()-ROW(%24H%246))*7%2C%20%22mmmm%2C%20dddd%20d%22)%20%26amp%3B%0A%20%20%22%20-%20%22%20%26amp%3B%0A%20%20TEXT(%24H%246%20%2B%20(ROW()-ROW(%24H%246))*7%2B6%2C%20%22mmmm%2C%20dddd%20d%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm trying to create a column that is dated as such:

January, Monday 4 - Sunday 10

January, Monday 11 - Sunday 17

where only the weeks start at Monday. I think this has something to do with the IF function? But I'm a complete beginner and do not understand that function.

 

Currently I have 

 

 

=TEXT(A6, "mmmm, ")&TEXT(H6,"mmm d")&IF(H6<>""," - "&TEXT(I6,"mmm d"),"") 

 

 

where A6 is the full date: Monday, January 4, 2021, H6 is the week and date: Monday 4, and I6 is the week and date: Sunday 10.

 

But, the output for what I currently have is:

January, Monday 4 - Sunday 10
January, Tuesday 5 - Monday 11. 

This is not what I'm looking for. Instead, I need a column of dates only starting on Monday. Is someone able to draft a formula for this? And if need for explanation, please explain it for beginners. 

2 Replies

@HannahClaire 

If in H6 start day when in A6

image.png

formula like

=TEXT($H$6 + (ROW()-ROW($H$6))*7, "mmmm, dddd d") &
  " - " &
  TEXT($H$6 + (ROW()-ROW($H$6))*7+6, "mmmm, dddd d")

and drag it down.

@HannahClaire 

You don't really need H6 and I6 for this.

I'd do the following:

Enter the start date January 4, 2021 in cell A6. It doesn't matter how you format it.

In A7, enter the formula =A6+7 and fill or copy this down as far as you want.

In another cell in row 6, enter the formula

 

=TEXT(A6,"mmmm, dddd d")&" - "&TEXT(A6+6,"dddd d")

 

Fill or copy down.

 

S0468.png