Can You Improve on this Date Calc?

[Smmiles]

Hello: I have created a custom date field with the formula below to calculate the cycle in which a task finishes. The Date8 column contains a date which indicates the start of the first cycle. I have a companion formula to calculate in which cycle a task starts. The problem is this. Every other cycle, the results are inaccurate IF the task starts or finishes on a Sunday which is the last day of the cycle. The results of the calculation will push that day into the next cycle, not its current cycle. Cycles are 2 weeks long, From Monday to Sunday. The "+.99" was added to get the calculation to become even this accurate. Without it, the results are wrong much more often. I want the formula to calc 100% accuracy 100% of the time. Can you help?

Round((([Finish]-[Date8]+0.99)/14)-0.5)

 

[John Beamish]

This seems to work:

cint((datediff("d",Start,Finish)+7.5)/14)

If this doesn't work for you then please reply with a table of 29 days (Nov
1, Nov 2, ... Nov 29) showing what the calculation shows and what it should
be.

JLB, PMP

Hello: I have created a custom date field with the formula below to

calculate the cycle in which a task finishes. The Date8 column contains a
date which indicates the start of the first cycle. I have a companion
formula to calculate in which cycle a task starts. The problem is this.
Every other cycle, the results are inaccurate IF the task starts or finishes
on a Sunday which is the last day of the cycle. The results of the
calculation will push that day into the next cycle, not its current cycle.
Cycles are 2 weeks long, From Monday to Sunday. The "+.99" was added to get
the calculation to become even this accurate. Without it, the results are
wrong much more often. I want the formula to calc 100% accuracy 100% of the
time. Can you help?

Round((([Finish]-[Date8]+0.99)/14)-0.5)

 

[Dean Carroll]

Check the times. An entry in Date8 will be 8:00 AM; an entry in finish will be 5:00 PM