Formula for Calculating # of Days

[pcar]

I have a spreadsheet with the following columns:
Col A - Start Date of Contract
Col B - End Date of Contract
Col C - Start Date of Warranty for Machine
Col D - End Date of Warranty for Machine
Col E - Number of Days on Warranty for the Machine,
WITHIN THE START AND END DATES OF THE CONTRACT
Col F - Number of Days Out of Warranty for the Machine,
WITHIN THE START AND END DATES OF THE CONTRACT

One line might have a "Start Date of Warranty" and "End
Date of Warranty" prior to the "Start Date of Contract";
another might have a "Start Date of Warranty" PRIOR to
the "Start Date of Contract with an "End Date of Warranty"
AFTER the "End Date of Contract"; another will have "Start
Date of Warranty" PRIOR to the "Start Date of Contract"
with an "End Date of Warranty" PRIOR to the "End Date of
Contract", etc, etc. I cannot seem to figure out an @if
formula that will take into account all the variations and
calculate Columns E and F for me. The number of days in
Columns E and F should add up to the total number of days
between start and end of contract; for example, if the
number of days between contract start and end is 365 (one
year), then depending upon the warranty start/end of a
machine, you might have 0 days of warranty and 365 days of
out of warranty; or 60 days of warranty and 305 days of
out of warranty; or 365 days of warranty and 0 days of out
of warranty, etc.

Would appreciate any assistance on this.....thanks.

 

[RWN]

I haven't tried to put this as a formula within a worksheet (I think a VBA
solution is the answer) but I think I can point you in the right direction
logic-wise (provided I've understood the scenario and I'm not too tired!).

MaximumDays = EndContract - StartContract.

If StartWarranty < StartContract then
DaysIn = EndWarranty - StartContract
Else
If Start-Warranty > EndContract
DaysIn = Zero
Else (the warranty starts within the, or is equal to the contract
period)
DaysIn = EndContract - StartWarranty
EndIf
EndIf

DaysOut = MaximumDays - DaysIn.

I usually use VBA rather than going nuts trying to fabricate some long
winded formula in a cell because, I admit that, I can get awfully confused
trying to debug it when it doesn't work.

Hope this helps.
--
Regards;
Rob

Please reply to the NG, I'm already up to my eyeballs in Nigerian/South
African get rich letters
as well as "Microsoft Critical Updates" et al.

 

[Earl Kiosterud]

Pcar,

Warranty within contract:
=MAX(0,MIN(D2,B2)-MAX(C2,A2))

Non-warranty within contract:
=MAX(0,C2-A2) + MAX(0,D2-B2)

I think these are right -- I get a little dizzy trying them all. Note that
they give date differences (Jan1 to Jan 3 is two days).

 

[Ron Rosenfeld]

I have a spreadsheet with the following columns:
Col A - Start Date of Contract
Col B - End Date of Contract
Col C - Start Date of Warranty for Machine
Col D - End Date of Warranty for Machine
Col E - Number of Days on Warranty for the Machine,
WITHIN THE START AND END DATES OF THE CONTRACT
Col F - Number of Days Out of Warranty for the Machine,
WITHIN THE START AND END DATES OF THE CONTRACT

I think the following formulas will work, but you need to check out all of the
variations.

Col E:

=D3-C3-MIN(D3-C3,((A3-C3)>0)*(A3-C3))-((D3-B3)>0)*(D3-B3)

Col F:

=B3-A3-E3


--ron