RE: Can this be done

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi JMB

Do you have an email address to which I could send you the table to look at ?
--
Steven.

In God we trust, all others we virus scan.


"JMB" wrote:

Never mind, that formula won't work 100% of the time (such as when the
interval is exactly six months).

It sounds like you first need to determine what the last service date was.
Assuming A1:A20 is your weekly dates, B1:B20 is the mileage readings for your
service dates, you could try entering in A22 (format the cell as a date)

=SUMPRODUCT(--(B1:B20=MAX(B1:B20)),A1:A20)

to find the last service date. In B22 enter the number of months (such as
6). In C22 enter

=DATE(YEAR(A22),MONTH(A22)+B22,DAY(A22))

to find the next service date (6 months from the last service date)

and (modified after seeing the good suggestions others posted)

=DATEDIF(TODAY(),C22,"M")&" Months, "&INT(DATEDIF(TODAY(),C22,"MD")/7)&"
Weeks, "&ROUND(MOD(DATEDIF(TODAY(),C22,"MD")/7,1)*7,0)&" Days"

to find the number of Months, Weeks, and Days between today's date and the
next service date. Change range references as necessary.




"JMB" wrote:

Assuming the last service date is in A1, the number of months until the next
service date is in B1, enter in C1

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(B1))

to get the next service date

and in D1
=DATEDIF(TODAY(),C1,"M")&" Months,
"&INT((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7)&" Weeks,
"&ROUND(MOD((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7,1)*7,0)&" Days"

to get the number of Months,Weeks,Days. Try it out and see if it gives the
results you want.


"elusiverunner" wrote:

I have a worksheet where I enter data on a weekly basis - (car servicing) at
present I have been able to total the distance travelled since the last
serviced. What I would like to be able to do is display how much time - (in
months, weeks and days) till the next service interval, knowing that each
service interval is three months or six months hence. In other words count
down the time from previous service to next service.

Can someone, firstly tell me if it can be done and secondly how can it be
done ??
--
Steven.

In God we trust, all others we virus scan.
.

Relevant Pages

  • RE: Can this be done
    ... "elusiverunner" wrote: ... In God we trust, ... "JMB" wrote: ...
    (microsoft.public.excel.worksheet.functions)
  • Inventory Scout 2.2.0.2, can it be applied to 5.1?
    ... Inventory Scout 2.2.0.2, can it be applied to 5.1? ... "In God we trust, all others we virus scan" ...
    (AIX-L)
  • >>>> TRUST GOD <<<<
    ... In God We Trust On Money ... Dollar Coin In God We Trust ... In God We Still Trust Lyrics ...
    (comp.text.tex)
  • Endtime Indications - 12/25/07
    ... If we do not let God into these everyday details, ... Would you like to have God's guidance for your life? ... But faith is not belief. ... Or must I say in all honesty, "I want God, and yet I don't yet trust ...
    (alt.gathering.rainbow)
  • Re: 9 Bills That Would Put Creationism in the Classroom
    ... The only reason I bring the age up with respect to what you ... trust in him for salvation. ... you walked up to the chair that you are currently sitting in at your ... (if you are God) ...
    (talk.origins)