Pete your formulas work great I got the same results but had to use several
different formalas in different cells to accomplished what you did in just 4
cells. Thanks a lot. but it still does not resolve my problem the use still
has to enter in dates for each year. I trying to aviod the user entering in a
wrong date. What I'm try to accomplish is to have the user put in the
from_date and the To_Date and have excell figure the number of weeks for each
year. exemple
From_Date 3/1/08 to 2/1/06
first year=1/1/08 to 3/1/08 wks days penalty 2008
2nd year=1/1/07 to 12/31/07 wks days penalty 2007
3rd year=2/1/06 to 12/31/06 wks days penalty 2006

"Pete_UK" wrote:
Year
The formulae I gave you return text values for the weeks and days, but
you can amend them to this:

D2: =IF(OR(A2="",B2=""),"",INT((B2-A2)/7))
E2: =IF(D2="","",MOD(B2-A2,7))

to return numbers directly. Then if you have your weekly rate and
daily rate in two other cells somewhere, eg R1 and R2, then the
penalty amount will be:

=D2*$R$1 + E2*$R$2

Format as currency and copy down as required.

Hope this helps.

Pete

On Mar 26, 12:40 pm, 904allen
wrote:
the penalty is set for a week at the beginning of each year based on average
wage in this state. thats why we need to brake down the time into wks. we
then divided the wk into 7 days for the daily rate sometimes the daily rate
is slightly higher then the wk rate divided by 7. we then take the wkly rate
times the number of wks and then add up the daily rate and add that to the
total wkly rate. example in a company is receiving a penaly for the period of
1/1/08 to 1/10/08 the peanlty would be 1 wk and 3 days added to the total of
the one wk.



"Pete_UK" wrote:
Thanks for feeding back - glad to help so far. If you can tell me what
the rules are for determining the penalty amount, then I might be able
to suggest a way of calculating it.

Pete

On Mar 26, 2:15 am, 904allen
wrote:
Thanks Pete I'm close. the penalty amount is a formula fot that year. each
year the penalty amt changes. Thats why I listed each year from the first day
to the last. the problem is I need to list each year in order to calculate
the penalty. the penalty is broken down by wks and days, the penalty is
different for the days then for the wks thats why the wks and days are
showen. if i can have the user just enter the From_Date to the To_Date that
will save me from having to make up a new work sheet each year. there are
many other calculations on this sheet for other areas not relate.

"904allen" wrote:
I have a sheet that figures dates, it can go back 3 years the user now puts
in the dates for each year needed, it looks like this
4/1/05 to 12/31/05 penalty amt. 39wks 2days Year2005
1/1/06 to 12/31 06 penalty amt. 52wks 1 day Year2006
1/1/07 to 12/31/06 penalty amt. 52wks 1 day Year2007
1/1/08 to 4/1/08 penalty amt. 13wks 1 day Year2008
what I would like to be able to do is change the sheet to have the user only
have to put in the date from to the date to and have the sheet formulate the
rest. The dates don't always go back 3 years it can go back any amount of
time but not longer then 3 years. I not sure how to proceed can any you
steer me in the right direction.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -