Split Date Formula

[JPAvila]

Hello,

I constantly work with product inventory and I would like to know how to split dates based on time periods (Calendar Years or Time Periods i.e. 4/1/16 - 3/31/17 or 10/1/16 - 9/30/17. Is there a formula I can use for this...

For Example I have the following product with time period availability of:

Product Start Date End Date
1660062 09/23/2017 07/31/2020


However,

If I were to split the dates by calendar year Jan - Dec, the results would be:

Product Start Date End Date
1660062 09/23/2017 12/31/2017
1660062 01/01/2018 12/31/2018
1660062 01/01/2019 12/31/2019
1660062 01/01/2020 07/31/2020


If I were to split the dates by periods April-2017 - March-2018, the results would be:

Product Start Date End Date
1660062 09/23/2017 03/31/2018
1660062 04/01/2018 03/31/2019
1660062 04/01/2019 03/31/2020
1660062 04/01/2020 07/31/2020


Please also keep in mind dates with 30, 31 or 28/29 days depending of leap year.

'I've been trying to figure out a solution and its driving me crazy. Please help.

Best,
JP

 

[CyrusTheVirus]

If you chose Oct-Sep with the same criteria as above, then how would you want it to look then?

 

[JPAvila]

Hi CyrusTheVirus,

Product Start Date End Date
1660062 09/23/2017 07/31/2020

If I were to split the dates by periods Oct - Sep, the results would be:

Product Start Date End Date
1660062 09/23/2017 09/30/2017
1660062 10/01/2017 09/30/2018
1660062 10/01/2018 09/30/2019
1660062 10/01/2019 07/31/2020

JP

 

[CyrusTheVirus]

Hi JP,

Sorry, i've been out of state this weekend. I should have some type of solution for you in the next couple days. Unless someone has a better idea (or some crazy formula), this will probably need to be accomplished by combining multiple formulas along with a good amount of reformatting your spreadsheet. Keep you posted.

 

[JPAvila]

Thanks for the update. I have not found a solution yet. So any ideas will help. Thanks.

 

[CyrusTheVirus]

Hi JP,

This ended up being a bit more complex that I'd hoped. Unless someone has a better/more clever/more efficient way of doing this, or if someone has a macro to do this (which is probably possible, but not my thing), then i'm hoping that the below could help you out?

Note first that columns J through P are helper columns which could be hidden if necessary.

And a disclaimer... please test this out. It appears to be working correctly, but date logic can become confusing (at least for me it can), so please test out the below before implementing.

Just copy the formulas down, and if you have any questions about any of this, then just let me know.

Excel Workbook
	A	B	C	D	E	F	G
1	Found in List	Product	Start Date	End Date	Period Start	Period End	Is 12 Month Period?
2	Yes	1660062	9/23/2017	7/31/2020	Jan	Dec	Yes
3	Yes	1660063	10/23/2017	3/31/2022	Apr	Mar	Yes
Dates

Excel Workbook
	I	J	K	L	M	N	O	P	Q	R
1	Product	Count	Start Date	End Date	Period Start	Period End	Start Increments	End Increments	Period Start	Period End
2	1660062	1	9/23/2017	7/31/2020	Jan	Dec	9/23/2017	12/31/2017	9/23/2017	12/31/2017
3	1660062	2	9/23/2017	7/31/2020	Jan	Dec	1/1/2018	12/31/2018	1/1/2018	12/31/2018
4	1660062	3	9/23/2017	7/31/2020	Jan	Dec	1/1/2019	12/31/2019	1/1/2019	12/31/2019
5	1660062	4	9/23/2017	7/31/2020	Jan	Dec	1/1/2020	12/31/2020	1/1/2020	7/31/2020
6	1660062	5	9/23/2017	7/31/2020	Jan	Dec	1/1/2021	12/31/2021
7	1660063	1	10/23/2017	3/31/2022	Apr	Mar	10/23/2017	3/31/2018	10/23/2017	3/31/2018
8	1660063	2	10/23/2017	3/31/2022	Apr	Mar	4/1/2018	3/31/2019	4/1/2018	3/31/2019
9	1660063	3	10/23/2017	3/31/2022	Apr	Mar	4/1/2019	3/31/2020	4/1/2019	3/31/2020
10	1660063	4	10/23/2017	3/31/2022	Apr	Mar	4/1/2020	3/31/2021	4/1/2020	3/31/2021
Dates

 

[JPAvila]

Thanks a lot for your help. I'm going to test it out and will give you my feedback.