 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Dates
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. 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 On Mar 26, 12:30 am, 904allen <904al...@discussions.microsoft.com> 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 [quoted text clipped - 7 lines] > time but not longer then 3 years. I not sure how to proceed can any you > steer me in the right direction. 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. > 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 [quoted text clipped - 7 lines] > time but not longer then 3 years. I not sure how to proceed can any you > steer me in the right direction. 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 <904al...@discussions.microsoft.com> 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 [quoted text clipped - 18 lines] > > - Show quoted text - 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. > 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 [quoted text clipped - 26 lines] > > > > - Show quoted text - 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 <904al...@discussions.microsoft.com> 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 [quoted text clipped - 37 lines] > > - Show quoted text - 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 > The formulae I gave you return text values for the weeks and days, but > you can amend them to this: [quoted text clipped - 57 lines] > > > > - Show quoted text - 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 On Mar 26, 9:52 pm, 904allen <904al...@discussions.microsoft.com> wrote: > 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 [quoted text clipped - 73 lines] > > - Show quoted text - 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. > 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 [quoted text clipped - 7 lines] > time but not longer then 3 years. I not sure how to proceed can any you > steer me in the right direction. 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 On Mar 27, 11:59 am, 904allen <904al...@discussions.microsoft.com> wrote: > 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 [quoted text clipped - 20 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.