LinkBack URL
About LinkBacks
I'm in the final stages of building a spreadsheet that calculates the time a person has worked in his life in years, days and months (it's for retirement purposes, it will be released for public use for laywers and federal justice workers).
The user inputs all the periods he has worked in his life in 2 columns, and then there's the third column that calculates the # of months between the begin and end dates.
[Start date] [End date] [# of months]
Job1_begin Job1_end [Job1_end - Job1_begin + 1]
Job2_begin Job2_end [Job1_end - Job1_begin + 1]
The formula to calculate the # of months between each one of these two dates is done (just for reference, if only 1 day inside a month is worked, it should count the entire month). Also, we always sum +1 because if the begin and start month/year are the same, the result would be 0, but per the previous rule we need to count 1.
But there's one problem / challenge: the same month can't be counted twice !
So if you have 3 periods like these ...
March 1st 2010 - April 1st 2010
April 2nd 2010 - April 5th 2010
April 6th 2010 - April 29th 2010
... you should count APRIL 2010 only one time. So the third column, in this example, would be like this:
March 1st 2010 - April 1st 2010 [2 months, march and april]
April 2nd 2010 - April 5th 2010 [0 months, because april/2010 has already been counted]
April 6th 2010 - April 29th 2010 [0 months, because april/2010 has already been counted]
So as you see, the formula in the third column has to loop trough all the other lines and make sure that its not counting the same month/year twice.
One thing the spreadsheet already does is to ensure that there are NO overlapping between the dates. But warning, the dates can be in any order, so we can have:
April 2nd 2010 - April 5th 2010 [1 months, because april/2010 hasn't already been counted]
April 6th 2010 - April 29th 2010 [0 months, because april/2010 has already been counted]
March 1st 2010 - April 1st 2010 [1 month, because april/2010 has already been counted]
I already created a formula that helped me get 99% in achieving my goal, but there's one scenario that it's failing. It uses a complicated joint of arrayformula and sumproduct and Month() and year(), but I'd rather not post it here because maybe a "fresh thinking start" can be helpfull. If nobody gets to a solution, I will post my broken solution and maybe we can evolve it, but it will be really hard even to explain what I'm doing now.
Register To Reply
Bookmarks