Date Calculation Query - Not your ordinary date calculations

[jamesrockford]

Having a bit of difficulty getting my head around this, appreciate any assistance, suggestions or guidance.  I am making multiple calculations, as various fields have different deadlines, so this is not an "all in one" solution.

 

I have 2 types of deadlines I am having difficulty with.  Then I have one weird anomaly situation, that maybe I will be able to easily resolve once I have a solution for the first 2. 

 

Some deadlines are based on years.  So "2 years" from X date or "6 years" from X date (inclusive, so the first day count).

 

Other deadlines are based on months, so "6 months" from X date or "3 months" from X date (inclusive, so first day counts).

 

I have leap year concerns.

 

---

Essentially, what I have is 2 dates:

 

A "Start Date" and an "End Date".

 

I want to create a calculation that tells me three things...

 

1) How many years have passed.  2)  Has it went beyond 2 years?  3) If it has not went beyond 2 years, how many DAYS are left until it hits the 2 year mark?

 

Example:

 

21/10/2012 to 21/10/2014 would be one day over the 2 year mark.  The 2 year mark, for purposes of my calculation, would be 20/10/2014.

 

21/10/2012 to 1/10/2013 would say that 0 years have passed, it has not went beyond 2 years and there are 384 days remaining.

 

 

I also have this same issue, as sometimes I have month deadlines.  So same thing.

 

A "Start Date" and an "End Date"

1) How many months have passed.  2) Has it went beyond 6 months?  3) if it has not went beyond 6 months, how many days are left until it hits the 6 month mark?

 

Example:

 

21/10/2012 to 21/04/2013 would be past the six month mark.  The six month mark, for purposes of my calculation, would be 20/04/13.

 

---

 

I also have this odd situation with one type of deadline.

 

There is a Start Date and a Pause Date; however, there is also a "Resume Date" and "End Date".

 

In this instance, there is a 2 year deadline; however, the clock stops ticking on the "pause date".  The clock does not start ticking again until SIX MONTHS AFTER the resume date.  On balance, the difference between the start date and end date cannot be 2 years or more, with consideration given to the pause and then the six months added time.

 

1) How many months have passed that COUNT. 2) Has it went beyond 2 years, with consideration to the pause and then the six months on resume?  3) if it has not went beyond, how many days were remaining.

 

Example

Start Date: 21/10/2012

Pause Date: 20/10/2013.

Resume Date: 21/10/2014

End Date: 20/11/2015

 

This would show that there were 19 months that passed, it did not go beyond 2 years, and that there were 5 months remaining (calculated into appropriate days).

 

---

 

This is how I handled "days"...it's probably hacky, but I'm still learning this and maybe it will show what I'm trying to achieve:

    

    If (Cases_SOL_EndDate - Cases_SOL_StartDate +1 > 42; 
    
         Cases_SOL_EndDate - Cases_SOL_StartDate +1 & " Days - Out of Time"; 
         If ( Cases_SOL_EndDate - Cases_SOL_StartDate +1 <2 ; 
             Cases_SOL_EndDate - Cases_SOL_StartDate +1 & " Day - In Time" & " - " & 42 - (  Cases_SOL_EndDate - Cases_SOL_StartDate +1) & " Days Remaining"; 
     
         If (42 - (Cases_SOL_EndDate - Cases_SOL_StartDate +1) = 1; "41 Days - In Time - 1 Day Remaining"; Cases_SOL_EndDate - Cases_SOL_StartDate +1 & " Days - In Time" & " - " & 42 - (  Cases_SOL_EndDate - Cases_SOL_StartDate +1) & " Days Remaining")))    
    

 

In the above scenario, there is a flat 42 day deadline and this is how I approached it.  I am having a bit of difficulty with deadlines that are determinable by months or years.

 

[comment]

That's a lot of questions for one thread. Let me start with this one:

 

A "Start Date" and an "End Date".

I want to create a calculation that tells me three things...

1) How many years have passed.  2)  Has it went beyond 2 years?  3) If it has not went beyond 2 years, how many DAYS are left until it hits the 2 year mark?

 

 

Try something along the lines of =

Let ( [
end = EndDate + 1 ;
age = Year ( end ) - Year ( StartDate ) - ( end < Date ( Month ( StartDate ) ; Day ( StartDate ) ; Year ( end ) ) ) ;
term = Date ( Month ( StartDate ) ; Day ( StartDate ) ; Year ( StartDate ) + 2 ) ;
rem = term - end
] ;
age & " years have elapsed; " & Case ( rem < 0 ; "More than two years have elapsed" ; rem & " days remaining" )
)

Using your example data of:

 

StartDate: 21/10/2012

EndDate: 21/10/2014

 

the result will be: "2 years have elapsed; More than two years have elapsed";

 

In your second example:

 

StartDate: 21/10/2012

EndDate: 1/10/2013

 

the result will be: "0 years have elapsed; 384 days remaining".

[comment]

Re your last question: unless your pause and resume dates occur exactly on days when a whole month has elapsed (as in your example), your calculation will be meaningless. You cannot piece together clusters of days into months, because months have different lengths.