Home

Grouping by dates in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-1189881%22%20slang%3D%22en-US%22%3EGrouping%20by%20dates%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189881%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20you%20group%20dates%26nbsp%3B%20that%20fall%20within%20specific%20date%20ranges%3F%20Is%20it%20by%20formula%20or%20by%20grouping%20by%20month%2C%20but%20the%20grouping%20won't%20work%20if%20you%20want%20an%20odd%20date%20range%20like%20from%20the%209th%20of%20one%20month%20to%20the%2010th%20of%20the%20next%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1189881%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-1190096%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20by%20dates%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190096%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F566217%22%20target%3D%22_blank%22%3E%40MMH11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20ways%20to%20handle%20your%20query%2C%20If%20you%20have%20single%20range%20of%20date%2C%20then%20I%20would%20recommend%20you%20using%20IF%20function%20(You%20can%20add%20multiple%20criteria%20in%20IF%20function%2C%20but%20formula%20will%20become%20too%20complex).%20I'm%20attaching%20Excel%20Workbook%20with%20few%20examples%20with%20IF%20%2C%20VLOOKUP%20%26amp%3B%20XLOOKUP.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20might%20be%20helpful.%26nbsp%3B%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%22Snag_4666368.png%22%20style%3D%22width%3A%20985px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172884iF571DC67E3ECEFCC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_4666368.png%22%20alt%3D%22Snag_4666368.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%20%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelexciting.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.ExcelExciting.com%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190244%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20by%20dates%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%20Sorry%2C%20I%20don't%20think%20I%20was%20very%20clear%20in%20my%20question.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EI%20have%20a%20list%20of%20employees%20with%20their%20work%20dates%20in%20a%20given%20year.%26nbsp%3B%20I%20want%20to%20insert%20a%20formula%20that%20indicates%20which%20period%20they%20fall%20in%20but%20it%20has%20to%20be%20a%20date%20from%2015th%20to%2014th%20of%20following%20month.%20So%20if%20the%20date%20is%2001%2F05%2F2020%20it%20should%20fall%20in%20the%20period%2012%2F15%2F2019-01%2F14%2F2020.%20What%20formula%20would%20do%20this%3F%3CBR%20%2F%3EEmployee%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Work%20Date%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20PERIOD%3CBR%20%2F%3EJohn%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2001%2F05%2F2020%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2012%2F15%2F2019-01%2F14%2F2020%3CBR%20%2F%3EJohn%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2001%2F15%2F2020%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2001%2F15%2F2020-02%2F14%2F2020%3CBR%20%2F%3EJohn%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2002%2F05%2F2020%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2001%2F15%2F2020-02%2F14%2F2020%3CBR%20%2F%3EJohn%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2003%2F06%2F2020%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2002%2F15%2F2020-03%2F14%2F2020%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190340%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20by%20dates%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190340%22%20slang%3D%22en-US%22%3E%3CP%3ECreate%20a%20help%20table%20with%20border-dates%20and%20Group%20names.%20Something%20like%3A%3C%2FP%3E%3CTABLE%20width%3D%22456%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E01%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E-%2001%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E01%2F15%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E01%2F01%2F2020%20-%2001%2F15%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E02%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E01%2F15%2F2020%20-%2002%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E03%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E02%2F01%2F2020%20-%2003%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E04%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E03%2F01%2F2020%20-%2004%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E05%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E04%2F01%2F2020%20-%2005%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E06%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E05%2F01%2F2020%20-%2006%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E07%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E06%2F01%2F2020%20-%2007%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E08%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E07%2F01%2F2020%20-%2008%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E09%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E08%2F01%2F2020%20-%2009%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E10%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E09%2F01%2F2020%20-%2010%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E11%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E10%2F01%2F2020%20-%2011%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22124.88%22%3E%3CP%3E12%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22330.12%22%3E%3CP%3E11%2F01%2F2020%20-%2012%2F01%2F2020%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20you%20can%20use%20Match%2Findex%20with%20match_type%201%20or%20vlookup%20with%20%E2%80%9Capproximate%20match%E2%80%9D%3Dtrue.%3C%2FP%3E%3CP%3EIf%20your%20support-table%20is%20in%20H1%3AI13%20something%20like%20this%20should%20word%3A%3C%2FP%3E%3CP%3E%3DVlookup(B1%2CH1%3AI13%2C2%2C1)%3C%2FP%3E%3CP%3E(Quick%20and%20Dirty%20translation.%20Play%20around)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190361%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20by%20dates%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F566217%22%20target%3D%22_blank%22%3E%40MMH11%3C%2FA%3E%26nbsp%3BAlternatively%2C%20one%20of%20the%20solutions%20in%20the%20attached%20workbook.%20The%20first%20one%20determines%20both%20the%20start%20and%20the%20end%20of%20the%20period%2C%20based%20on%20the%20start%20day%20(15%20in%20your%20case).%20It%20uses%20the%20fairly%20neat%20EOMONTH%20function.%20The%20second%20option%20combines%20start%20and%20end%20of%20the%20period%20into%20one%20text%20string.%3C%2FP%3E%3CP%3ECell%20B1%20contains%20the%20start%20day%20of%20your%20period%20and%20I%20gave%20it%20the%20name%20%22from%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

How do you group dates  that fall within specific date ranges? Is it by formula or by grouping by month, but the grouping won't work if you want an odd date range like from the 9th of one month to the 10th of the next month.

 

7 Replies
Highlighted

Hi @MMH11 

 

There are many ways to handle your query, If you have single range of date, then I would recommend you using IF function (You can add multiple criteria in IF function, but formula will become too complex). I'm attaching Excel Workbook with few examples with IF , VLOOKUP & XLOOKUP.

 

Hope this might be helpful. 

 

Snag_4666368.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

 

Highlighted

@Faraz Shaikh  Sorry, I don't think I was very clear in my question. 

 

I have a list of employees with their work dates in a given year.  I want to insert a formula that indicates which period they fall in but it has to be a date from 15th to 14th of following month. So if the date is 01/05/2020 it should fall in the period 12/15/2019-01/14/2020. What formula would do this?
Employee     Work Date      PERIOD
John         01/05/2020         12/15/2019-01/14/2020
John         01/15/2020          01/15/2020-02/14/2020
John         02/05/2020          01/15/2020-02/14/2020
John         03/06/2020          02/15/2020-03/14/2020

Highlighted

Create a help table with border-dates and Group names. Something like:

01/01/2020

- 01/01/2020

01/15/2020

01/01/2020 - 01/15/2020

02/01/2020

01/15/2020 - 02/01/2020

03/01/2020

02/01/2020 - 03/01/2020

04/01/2020

03/01/2020 - 04/01/2020

05/01/2020

04/01/2020 - 05/01/2020

06/01/2020

05/01/2020 - 06/01/2020

07/01/2020

06/01/2020 - 07/01/2020

08/01/2020

07/01/2020 - 08/01/2020

09/01/2020

08/01/2020 - 09/01/2020

10/01/2020

09/01/2020 - 10/01/2020

11/01/2020

10/01/2020 - 11/01/2020

12/01/2020

11/01/2020 - 12/01/2020

 

Then you can use Match/index with match_type 1 or vlookup with “approximate match”=true.

If your support-table is in H1:I13 something like this should word:

=Vlookup(B1,H1:I13,2,1)

(Quick and Dirty translation. Play around)

 

 

Highlighted

@MMH11 Alternatively, use one of the solutions in the attached workbook. The first one determines both the start and the end of the period, based on the start day (15 in your case). It uses the fairly neat EOMONTH function. The second option combines start and end of the period into one text string.

Cell B1 contains the start day of your period and I gave it the name "from".

 

Highlighted

<ed>sorry. i missed Rinys last  answere. This is probably a duplicate.</ed>


Generic formula. Same reservation as before. It’s an untested translation.
=TEXT(EOMONTH(B1,-2)+15,"MM/DD/YYYY")&"-"&TEXT(EOMONTH(B1,-1)+14,"MM/DD/YYYY")

Highlighted

@MindreVetande  @Riny_van_Eekelen  @Faraz Shaikh 

Would you know why Excel can't do this period automatically when selecting Grouping by month in a pivot table? Maybe there is a period option?

Highlighted

@MMH11 Well, I guess Excel uses standard periods such as calendar weeks, months, quarters and years. And then you can group by x number of days (like 28 days = 4 weeks). Grouping from the 15th of one month till the 14th of the next may be 30 days one month, 31 days another and sometimes 28 or 29 days. So, thats not a fixed number of days either. But why Excel can't do it by default, I can't tell. Perhaps, that's why they came up with EOMONTH.

Related Conversations
Is there any professional support for my Smart Index?
JUSER-C in Excel on
0 Replies
Sort/filter cells with picture
Drin365 in Excel on
4 Replies
Copying random selection
eiregobra in Excel on
5 Replies
Transposing data for better analysis
gckcmc in Excel on
1 Replies
LARGE green triangle
Summit83 in Excel on
1 Replies