Formula to calculate new delivery date due

Maths was never my forte at all, and I have just driven myself to the brink to work out how to formulate this calculation, and hope someone can help

The quandry is what will the next delivery date be, based on the following information

18kva Generator, on a qtr load, uses 24 litres a day (1ltr per hour), the fuel tank is 2000ltrs, and it runs 40hrs over a 5 day week (8hrs a day) the last delivery date was 1st February

100kva Generator, on a qtr load, uses 120 litres a day (5ltr per hour), the fuel tank is 2000 litres, and it runs 24/7, the last delivery date being 1st February

What will based on the above useage, the next delivery date be?



Thanks
 Fuel-consumption-for-yedl.xlsx
Babypink2807Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
You can get the number of days by dividing the capacity (2000) by the number of litres used in a day, which would be 24*8 = 192 so the result is approximately 10 workdays....so if you use WORKDAY function that will be this calculation

=WORKDAY(G5,E5*5/F5/C5)

[assuming that F5 contains just the number 40, without text]

For 24/7 operation then that would be

=G5+2000/120

both calculations assume that the tank will be full after a delivery.....

regards, barry
0
Amitkumar PSr. ConsultantCommented:
Find the updated sheet. I've added a column (with yellow back ground) Usage/Week (in Hrs). Provide the weekly hourly usage (max 168 hrs) in that column and check the Next delivery date column (with green background). Fuel-consumption-for-yedl.xlsx
0
Babypink2807Author Commented:
Thank you for the file attachment, now I maybe not getting it right, but as the fuel rate goes up say to say 120 ltrs a day as opposed to the 24 litrs a day, the refuelling should be more regular which the spreadsheet does not seem to do?

As an example if I put the figure 168 in the useage column which equates to 24/7 running and set the last delivery date to 1 Feb 2011 the higher the fuel consumption then surely the next delivery date will get less as I go down the table.  On the table atatched its all the same?

Sorry to be a pain
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

barry houdiniCommented:
Did you try the formula I suggested? For 24/7 that would be

=G5+E5/C5

If totals look the same try pressing F9 key to re-calculate.....

Also I'm a little unclear on when you want the MG calculation and when it's 24/7, what determines that, do you want that automated?

regards, barry
0
Babypink2807Author Commented:
Terribly sorry

My tank size is 2000 litres, I have a generator which is 18kva and is running 40hrs over a five day week (40/5) and each day (if it was running 24/7) it would use 24 litres (1 ltr per hour)

So as this generator is running 40hrs over five days that would mean each day it runs 8hrs so using 8litres a day (8hrx5days =40hrs).  With the fuel tanks capacity of 2000 litres this means theoretically it needs to be refuelled every xxxx days

Sorry now you know why I have a headache about it :-)
0
barry houdiniCommented:
OK on that basis then - using 1 litre an hour will mean that your tank will last for 2000/8 days = 250 days - as those are just working days (Monday to Friday) you can use WORKDAY function similar to the one I initially suggested but with an additional multiplication by 24, e.g. in  H5 copied down

=WORKDAY(G5-1,CEILING(E5*5*24/F5/C5,1)))

250 work days is almost a year so the result for H5 is 1/16/2012

Copy that formula down and when you get to H22 where you are using 71 litres per hour that's almost 4 days so you'll need to refuel on 2/4/2011.

The calculation might be different depending on exactly when re-fuelling takes place, for 71 litres an hour you use 568 in a day, so assuming you include 1/2/2011 (e.g. re-fuelling takes place at the start of that day) then you'll run out half way through the 4th....

see attached

regards, barry
26921398.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amitkumar PSr. ConsultantCommented:
Added one more column (with yellow background). Put litres of usage in an hour in that column.

Check and update me.
Fuel-consumption-for-yedl.xlsx
0
Babypink2807Author Commented:
Hi guys

Will try this today thank you and let you know the outcome.  I really do appreciate this help - thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.