Hi all,
I have an excel file.
Sheet 1 (NPI Timelines) contains a list of project milestones (multiple projects, multiple milestones, some may have the same end date) with respective end dates associated to each milestone. The other sheet has a calendar format speadsheet (January, Mondays to Friday from 1 to 31) which pulls the milestone names under the respective date int he calendar spread sheet.
The change i would like to make is that the sheet with the project milestone (NPI Timelines) should now have a start date and an end date instead of just the end date.
what I'm having trouble with is getting that milestone to show up on the different dates between the start and the end date. For example if milestone 1 now has a start date of 1-jan-2017 and end date of 4-jan-2017, i want the calendar to show the name of the milestone under all of the days, 1-jan-2017, 2-jan-2017, 3-jan-2017, 4-jan-2017.
The formulas I use currently to pull the milestone name on the calendar sheet under the respective date is the below one. I don't know how to modify to be able to pull the milestone name associate today's date (on the calendar) falls between the start date and end date of the milestone.
=IF(ISERROR(INDEX('NPI Timelines'!$B:$C,SMALL(IF('NPI Timelines'!$B:$C=$E$3,ROW('NPI Timelines'!$B:$C)),ROW(1:1)),2)),"",INDEX('NPI Timelines'!$B:$C,SMALL(IF('NPI Timelines'!$B:$C=$E$3,ROW('NPI Timelines'!$B:$C)),ROW(1:1)),2))
The above formulas I got from googling and it works but it only enters the milestone name under the end date; I would like it to show up under all of the days that fall between the start date and end date.
I have attached my spreadsheet as an example.
Your help is greatly appreciated.
Thank you in advance.
I have an excel file.
Sheet 1 (NPI Timelines) contains a list of project milestones (multiple projects, multiple milestones, some may have the same end date) with respective end dates associated to each milestone. The other sheet has a calendar format speadsheet (January, Mondays to Friday from 1 to 31) which pulls the milestone names under the respective date int he calendar spread sheet.
The change i would like to make is that the sheet with the project milestone (NPI Timelines) should now have a start date and an end date instead of just the end date.
what I'm having trouble with is getting that milestone to show up on the different dates between the start and the end date. For example if milestone 1 now has a start date of 1-jan-2017 and end date of 4-jan-2017, i want the calendar to show the name of the milestone under all of the days, 1-jan-2017, 2-jan-2017, 3-jan-2017, 4-jan-2017.
The formulas I use currently to pull the milestone name on the calendar sheet under the respective date is the below one. I don't know how to modify to be able to pull the milestone name associate today's date (on the calendar) falls between the start date and end date of the milestone.
=IF(ISERROR(INDEX('NPI Timelines'!$B:$C,SMALL(IF('NPI Timelines'!$B:$C=$E$3,ROW('NPI Timelines'!$B:$C)),ROW(1:1)),2)),"",INDEX('NPI Timelines'!$B:$C,SMALL(IF('NPI Timelines'!$B:$C=$E$3,ROW('NPI Timelines'!$B:$C)),ROW(1:1)),2))
The above formulas I got from googling and it works but it only enters the milestone name under the end date; I would like it to show up under all of the days that fall between the start date and end date.
I have attached my spreadsheet as an example.
Your help is greatly appreciated.
Thank you in advance.