Create interval based on a specific starting date

[Motz]

Hi,

I'm analysing a bunch of different projects. All projects had deadline 6 months from their starting date. Therefor I need a formula that creates a new field telling whether the date is in the first or the third month of the project plan. 

I have attached a sample of the data. But it could look like this:

ID	StartingYear	StartingMonth	Year	Month	Time
0001	2017	December	2017	November	0
0001	2017	December	2017	December	1,5
0001	2017	December	2018	January	3
0001	2017	December	2018	February	2
0001	2017	December	2018	March	6,2
0001	2017	December	2018	April	7,5
0001	2017	December	2018	June	9
0002	2017	June	2017	June	2
0002	2017	June	2017	July	4,1

And I want it to look like this:

ID	StartingYear	StartingMonth	Year	Month	Time	ProjectMonth
0001	2017	December	2017	November	0	Null
0001	2017	December	2017	December	1,5	1
0001	2017	December	2018	January	3	2
0001	2017	December	2018	February	2	3
0001	2017	December	2018	March	6,2	4
0001	2017	December	2018	April	7,5	5
0001	2017	December	2018	June	9	6
0002	2017	June	2017	June	2	1
0002	2017	June	2017	July	4,1	2

Thanks in advance.

[Paulteryx · Accepted solution]

Hi,

I've put together a little sample with your data. Essentially you want to merge your month and year columns in the yyyy-mon and yyyy-month format. Then using the DateTime tool we can convert that to a date that we can do math with (2017-01-01,  2018-04-01 etc). Setting up a datetimediff() equation in the formula tool and requiring it return the results in number of months I believe will give you what you're looking for. That said, I noticed in your sample data, you have some starting year/months that before the year and month fields. Therefore in this example you get negative numbers.

[ponraj · Accepted solution]

Samle workflow as below. 

[Motz]

Thank you very much!