+ Reply to Thread
Results 1 to 9 of 9

Stoping a formula increasing the daily accrued costs when an end date is added

  1. #1
    Registered User
    Join Date
    09-03-2014
    Location
    London
    MS-Off Ver
    2014
    Posts
    4

    Stoping a formula increasing the daily accrued costs when an end date is added

    Hi All,

    I have been hanging around the forum a lot of late and have found it incredibly helpful. Thanks for that.

    I am totally stuck on something that I hope somebody can help with.

    I have attached the spreadsheet to illustrate my problem.

    I have a date hired and a date off-hired column, along with daily hire costs and weekly hire costs.

    I have managed to enter a formula that calculates the cost accrued since the date of hire, and also conditional formatting to highlight when a hired item is over the expected number of hire days.

    What I am struggling with is two things.

    1) Getting the current total to switch to 'weekly rate' after 5 days
    2) Getting the current total to stop accruing costs when data is entered into the 'off-hired' column.

    I hope somebody here can help.

    ThanksBuyers-hire.xlsx

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    Maybe this is what you are looking for. Enter in M2 and copy down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    09-03-2014
    Location
    London
    MS-Off Ver
    2014
    Posts
    4

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    Hi There

    Thanks for that. That seems to go some way to making it work, but not quite.
    The current total seems to get stuck with 5 days hire on only no matter how many days the hire period is. It doesn't take into account the weekly hire costs.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    I don't understand what you are referring to. What are "weekly hire costs"? What column is it in? Does "Off-hired" mean no longer working?

    Perhaps you could enter some data into your file with the expected results as the description I find quite confusing.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    After carefully re-reading your problem and downloading your worksheet, this might be what you are looking for. I understand the Date Off-hired is the date no longer in effect. Also, L2 is a weekly rate and once 5 days have elapsed, the weekly rate kicks in so that the first 5 days are at one rate and after that the rest of the days are at the weekly rate...

    I'm really not 100% sure of the calculation that you want but this I think is closer.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-03-2014
    Location
    London
    MS-Off Ver
    2014
    Posts
    4

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    That is great thanks very much. Really helpful.

    It isn't quite as I need but really close and it puts me much farther on that I was.

    Where I am trying to get to is:

    After the 5 days of hire has elapsed, the weekly rate will then replace the 5 days cost that has been accrued. At the moment as you said after 5 days has elapsed, the weekly rate is charged on a daily basis. I probably didn't explain that very well.

    So if a hire charge is 100 per day(K2) and 450 per week (L2), after 4 days the figure in M2 will be 400. When the next day arrives and the item is still on hire, the cost will then revert to 450 until 7 days time when it will increase to 900 and so on.

    I really appreciate your help as everything else is spot on.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    The -5 in the formula was to take the first 5 days away from the week calculations then that was added to the weekly rate. The formula would become something like this ... probably not exactly right.... no time for testing right now.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-03-2014
    Location
    London
    MS-Off Ver
    2014
    Posts
    4

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    Hmm not quite right, but thanks. If you manage to text it out then let me know. I will tinker around with it. Thanks again

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Stoping a formula increasing the daily accrued costs when an end date is added

    You might be better off if you made a table with the number of days with the appropriate rate beside them. Then, all you would have to do is to subtract the beginning date from the current date to get the number of days and look that up in the table to get the rate. If the rates change, all you have to do is to adjust the table to reflect the changes.

    The more conditions that you put into the formula the more complicated it becomes especially if the rates go up and down dependant upon the length of time.

    This small example might give you some ideas.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Bill costs workout for running year on daily basis
    By wykoems in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-20-2013, 12:18 PM
  2. Bill costs workout for running year on daily basis
    By wykoems in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 05:31 AM
  3. Formula to calculate pto accrued ytd mtd
    By dharms in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-12-2013, 10:52 AM
  4. Cascading increasing costs
    By Xrull in forum Access Tables & Databases
    Replies: 7
    Last Post: 09-09-2013, 11:15 PM
  5. Accrued Vacation Time based on Anniversary Date
    By Mustang03 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2010, 02:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1