i can't seem to get my head around this formula. i have a range of project data where each record has start, end, and change dates. Some of these records begin prior to the start of the financial year and some start within the financial year and then end after the end of the fin year. The change dates represent a change in responsibility for the project. For each project with a change date, a new line is entered with the new details but still the original start and end dates. For the first Project below, there are multiple changes, with the change date representing the date of the change. Each change/new line I have given a line number using a simple formula which increases by 1 if the project number appears in the cell above.
In column I, I count the total number of project days within the financial year. the result is aligned to the first record number for each project using this formula:
this says, if A is not 1 (the first project record), return 0.
if A contains 1, and the start and finish dates are not the same, then compare start and finish dates of the project with the beginning and end dates of the fin year, and return the difference (which is either an amount in year or zero if the start and end dates are outside of the fin year).
if the project started and finished on the same day, and is within the fin year, we allocate 1 day.
all good.
the problem i am having is actually identifying which changes are within the fin year based on the change dates. So, i thought i would use the same methodology as I used to get total project days within the year.
I don't need to put the values only on the first line of each project so that initial part is gone. (If(A6>1,0)
please bear with me.
if start and end dates are different then
if(AND($A6=1,$A7=1) ..... identify whether a new project starts on the line below: if it does use the change dates in and out (Trans Date In and Trans Date Out above) compared to the fin year dates to determine whether or not that single line project's days are counted
BUT
IF(AND($A6=1,$A7>1) ..... if this is the first line of a multi line project (ie has changes), we need to use the change dates to count the days.
so once again, compare to the fin year dates... MAX(0,MIN($H7-1,$I$2)-MAX($H6,$I$3)+1) where H7 is the date of the next change and H6 is the date of this change. so for the first line of a project, the change date = start date.
then, if IF $A6<>1 or this is a subsequent line in the project, compare this change date to the previous one, and if they fall within the fin year, rack em up.
the flip side of the initial If statement, where Start and End dates are the same, so long as these dates are within the fin year, add 1.
the problem is, with my multi line projects, where the change falls on the start of the fin year (first project line 3), i am getting 0.
someone save me please. where do i adjust the formula to account for these without losing any others or gaining ones i don't want?
it might help to know, project rules stipulate that we count days by the end date. that is, if a project starts on 2 August and finishes on 3 August, its counted as only 1 project day. Starting and finishing on the same day is 1 project day. In context, if a project started 29/06/2018 (Project 1) and ended 5 July 2018, we count July 1 to 5 each as a day, so 5 project days within the fin year. If however, a project started 30/6/2019, the end of the fin year, no days would be counted until next year.
Rich (BB code):
Order data by Proj Nbr 1 Start Date Fin Yr End 30/06/2019 Change Date Fin Yr Start 1/07/2018 A B C D E F G H I J Record List Nbr REPORTING_MONTH Proj Nbr Start Date End Date TRANS_DATE_IN TRANS_DATE_OUT Change DATE In Year In Year By Day 1 B937866-12 29/06/2018 6/07/2018 29/06/2018 6/07/2018 29/06/2018 5 0 2 B937866-12 29/06/2018 6/07/2018 29/06/2018 6/07/2018 30/06/2018 0 0 3 B937866-12 29/06/2018 6/07/2018 29/06/2018 6/07/2018 1/07/2018 0 0 4 B937866-12 29/06/2018 6/07/2018 29/06/2018 6/07/2018 2/07/2018 0 1 5 B937866-12 29/06/2018 6/07/2018 29/06/2018 6/07/2018 3/07/2018 0 1 6 B937866-12 29/06/2018 6/07/2018 29/06/2018 6/07/2018 4/07/2018 0 1 7 B937866-12 29/06/2018 6/07/2018 29/06/2018 6/07/2018 5/07/2018 0 1 1 B937866-13 7/08/2018 10/08/2018 7/08/2018 10/08/2018 7/08/2018 3 1 2 B937866-13 7/08/2018 10/08/2018 7/08/2018 10/08/2018 8/08/2018 0 1 3 B937866-13 7/08/2018 10/08/2018 7/08/2018 10/08/2018 9/08/2018 0 1
<tbody>
</tbody>In column I, I count the total number of project days within the financial year. the result is aligned to the first record number for each project using this formula:
Rich (BB code):
=IF(A6>1,0,IF($D6<>$E6,MAX(0,MIN($E6-1,$I$2)-MAX($D6,$I$3)+1),IF(AND($D6=$E6,$D6>=I$3,$E6<=I$2),1,0)))this says, if A is not 1 (the first project record), return 0.
if A contains 1, and the start and finish dates are not the same, then compare start and finish dates of the project with the beginning and end dates of the fin year, and return the difference (which is either an amount in year or zero if the start and end dates are outside of the fin year).
if the project started and finished on the same day, and is within the fin year, we allocate 1 day.
all good.
the problem i am having is actually identifying which changes are within the fin year based on the change dates. So, i thought i would use the same methodology as I used to get total project days within the year.
Rich (BB code):
=IF($D6<>$E6,IF(AND($A6=1,$A7=1),MAX(0,MIN($G6-1,$I$2)-MAX($F6,$I$3)+1),IF(AND($A6=1,$A7>1),MAX(0,MIN($H7-1,$I$2)-MAX($H6,$I$3)+1),MAX(0,MIN($H6-1,$I$2)-MAX($H5,$I$3)+1))),IF(AND($D6=$E6,$D6>=I$3,$E6<=I$2),1,0))I don't need to put the values only on the first line of each project so that initial part is gone. (If(A6>1,0)
please bear with me.
if start and end dates are different then
if(AND($A6=1,$A7=1) ..... identify whether a new project starts on the line below: if it does use the change dates in and out (Trans Date In and Trans Date Out above) compared to the fin year dates to determine whether or not that single line project's days are counted
BUT
IF(AND($A6=1,$A7>1) ..... if this is the first line of a multi line project (ie has changes), we need to use the change dates to count the days.
so once again, compare to the fin year dates... MAX(0,MIN($H7-1,$I$2)-MAX($H6,$I$3)+1) where H7 is the date of the next change and H6 is the date of this change. so for the first line of a project, the change date = start date.
then, if IF $A6<>1 or this is a subsequent line in the project, compare this change date to the previous one, and if they fall within the fin year, rack em up.
the flip side of the initial If statement, where Start and End dates are the same, so long as these dates are within the fin year, add 1.
the problem is, with my multi line projects, where the change falls on the start of the fin year (first project line 3), i am getting 0.
someone save me please. where do i adjust the formula to account for these without losing any others or gaining ones i don't want?
it might help to know, project rules stipulate that we count days by the end date. that is, if a project starts on 2 August and finishes on 3 August, its counted as only 1 project day. Starting and finishing on the same day is 1 project day. In context, if a project started 29/06/2018 (Project 1) and ended 5 July 2018, we count July 1 to 5 each as a day, so 5 project days within the fin year. If however, a project started 30/6/2019, the end of the fin year, no days would be counted until next year.
