Formula Help Needed to Spread Days Across Months/Years

  • Thread starter Thread starter pcar
  • Start date Start date
P

pcar

Earlier I posted the following request, asking for a
formula to calculate Columns E and F:
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
Click to expand...

A gentleman by the name of Ron responded with the
following formulas, which work absolutely great:
Col E:
=D3-C3-MIN(D3-C3,((A3-C3)>0)*(A3-C3))-((D3-B3)>0)*(D3-B3)
Col F:
=B3-A3-E3

Now, however, they have asked me to spread out the total
number of days on warranty (Col E) and total number of
days out of warranty (Col F)into by month, for the term of
the contract, starting with the start date of the
contract. Any ideas on how to do this with a formula? I
am not capable of doing arrays or any VBA stuff
unfortunately. I would have to have 2 entries for each
month - one for # of days on warranty and another for
number of days out of warranty.

Can anyone assist?
 
Good afternoon Pcar -
I need more information about how Column A and B affect the calculations.
When you reply - either supply an example or ZIP or STUFF a sample file and send it to me.
For the rest -
Think we can help you with that part:

Column E - Subtract c from d and format the cell as number general (=D2-C2)
Column F - Subtract todays date from D (=Now()-D2)

Thanks,
Jon Barchenger
--------------------
**Content-Class: urn:content-classes:message
**From: "pcar" <[email protected]>
**Sender: "pcar" <[email protected]>
**Subject: Formula Help Needed to Spread Days Across Months/Years
**Date: Thu, 13 Nov 2003 14:41:19 -0800
**Lines: 36
**Message-ID: <[email protected]>
**MIME-Version: 1.0
**Content-Type: text/plain;
** charset="iso-8859-1"
**Content-Transfer-Encoding: 7bit
**X-Newsreader: Microsoft CDO for Windows 2000
**X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
**Thread-Index: AcOqN0FR2XKaTLAaQCusrgnr76bsvg==
**Newsgroups: microsoft.public.excel.worksheet.functions
**Path: cpmsftngxa06.phx.gbl
**Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:171226
**NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**Earlier I posted the following request, asking for a
**formula to calculate Columns E and F:
**
**>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
**
**A gentleman by the name of Ron responded with the
**following formulas, which work absolutely great:
**Col E:
** =D3-C3-MIN(D3-C3,((A3-C3)>0)*(A3-C3))-((D3-B3)>0)*(D3-B3)
**Col F:
** =B3-A3-E3
**
**Now, however, they have asked me to spread out the total
**number of days on warranty (Col E) and total number of
**days out of warranty (Col F)into by month, for the term of
**the contract, starting with the start date of the
**contract. Any ideas on how to do this with a formula? I
**am not capable of doing arrays or any VBA stuff
**unfortunately. I would have to have 2 entries for each
**month - one for # of days on warranty and another for
**number of days out of warranty.
**
**Can anyone assist?
**
**
**
**
**
**
 
Back
Top