Forum Discussion
Grouping by dates in Excel?
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.
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
ExcelExciting 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
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".
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)
<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")Deleted Riny_van_Eekelen ExcelExciting
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?
