Dates

[904allen]

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.

 

[Pete_UK]

I would suggest that you put the dates in two separate columns - one
for from_date and the other for to_date - assume these are columns A
and B. I'm not sure what your "penalty amt." is meant to be, but if
this is just text than you can copy it down as required. You can get
the weeks with this formula in D2:

=IF(OR(A2="",B2=""),"",INT((B2-A2)/7)&" wks")

This ensure that you only get a result if both dates are entered. You
can put this formula in E2:

=IF(D2="","",MOD(B2-A2,7)&" days")

to give you the days, and this one in F2:

=IF(B2="","","YEAR"&TEXT(B2,"yyyy"))

to give you the year from the to_date field.

Copy all these formulae down the columns as far as you need them.

Hope this helps.

Pete

 

[904allen]

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.

 

[Pete_UK]

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

 

[904allen]

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]

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

 

[904allen]

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

:
Year

 

[Pete_UK]

I think I see what you want to do, but before I give you the formulae
to accomplish that, can I ask why you can't just put the overall start
date and end date in A2 and B2 and arrive at something like:

01/02/2006 01/03/2008 108 wks 3 days

I'm not sure why you need to break it down into specific years.

Anyway, if you do then put the start date in A1 and the final date in
B1 (or get your Users to do so), and then put the following formulae
in the cells stated below:

A2: =A1

B2: =IF(A2="","",IF(DATE(YEAR(A2),12,31)>B$1,B$1,DATE(YEAR(A2),
12,31)))

A3: =IF(OR(A2="",B2=B$1),"",DATE(YEAR(A2)+1,1,1))

Copy B2 into B3, and then copy A3:B3 down into as many cells as you
feel you need. All of the cells in columns A and B should be formatted
as a date in the format you prefer.

The other formulae I gave you will still work with this set up, so
they can be copied down the same number of rows as your date formulae.

Note that I have used the approach outlined in your first post, i.e.
listing the oldest dates first, rather than in your latest posting
which lists the most recent dates first.

So now your Users only have to enter the start date into A1 and the
final end date into B1 and the rest of it will automatically appear.

I hope this is finally what you want.

Pete

 

[904allen]

once the user has the total penalty for each year they then have to calculate
a percentage based on the type of work being done by the person that is going
to receive the penalty. This information is entered in another program.
there are hundreds fo different figures for type of work being done and part
of my workbook already figures this part of the peanlty out, the precentages
for the type of work being done also chages every year. But it all starts
with figuring out the amount of the total penalty by year before me can add
the type of work and the percentage. there are onther facts figued into this
but all of them rely on being able to have the penalty by year. Hope this
makes sense.

 

[Pete_UK]

I'm not sure how far you want to take this, as you mention that
another program is used beyond the Excel application. If you have
different rates for different years then instead of the simple
approach I mentioned earlier of having the weekly and daily rates in
R1 and R2, you could build up a table of rates by year and then use
the VLOOKUP function to extract the appropriate rate.

A further thought on the dates issue - if your dates are meant to be
inclusive, then you will have to add 1 to the formulae, i.e.:

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

If your dates are from 1st Jan 2007 to 31st Dec 2007 then this will
correctly return 52 wks and 1 day.

Hope this helps.

Pete