Create interval based on a specific starting date
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you very much!
