Formulas for tank volume calculation by using DATE

billybarty

New Member
Joined
Feb 16, 2011
Messages
13
I need to make a sheet that will let me know the volume in a container by using the date and i don't know how to proceed. I have columns with the date the container was last filled and the volume on that day, a column for the rate of consumption per day, a column for what is the volume today, a column for what the volume will be in x number of days (currently 7) and a column for the date that the volume will be 40 liters or less. I had tried this before at my previous job and had some help with this on this forum but am unable to find the post. Also, I am using Excel 2003 at this position and it doesn't have as much built-in help on building formulas. Any help is appreciated
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
I see there have been many views but no replies as of yet so maybe more info is needed.
F=date filled
G=Volume
H=Rate
I=Current volume today
J=volume in x days
K=date volume is < 40 liters

Thanks for any help
 
Upvote 0
E​
F​
G​
H​
2​
date filled​
04/17/2014​
F2: Input
3​
capacity​
1000​
litersF3: Input
4​
rate​
29​
liters/dayF4: Input
5​
current vol​
652​
litersF5: =F3 - (TODAY() - F2) * F4
6​
x​
5​
daysF6: Input
7​
volume in x days​
507​
litersF7: =F3-(TODAY() + F6 - F2)*F4
8​
date vol = 40​
05/20/2014​
F8: =F2 + (F3-40) / F4
 
Upvote 0
By using the table above I have entered the formulas and I return #value for all of them. I don't know how to enter a table here like you have above but hopefully I can explain it good enough. For Current Volume column I I have =G2(TODAY()-F2)*H2. For Volume in 7 days column J I have =G2-(TODAY()+7-F2)*H2. For Date Volume is <40L I have =F2+(G2-40)/H2. Am I close to having them right or did I totally misunderstand your table?
 
Upvote 0
For Current Volume column I I have =G2(TODAY()-F2)*H2.
Multiplication is not implicit in Excel: =G2*(TODAY()-F2)*H2

But why not just use the formulas I used as a confidence-builder, and then make changes from there?
 
Upvote 0
My sheet is setup differently than the one you posted. The items you have entered in the E column are all the items I have entered as individual columns F to K so the formulas don't work as they are above. I saved my sheet as a jpeg to better show what I need to do but it is asking for a URL when I try to insert image in this post.
 
Upvote 0
You could enter the data as I have, and then drag the formulas to the cells where you want them.

If you had posted your actual layout, I would have used it.
 
Upvote 0
I've tried to post my layout but can't figure out how to, I can't paste the image into this reply. I put in the multiplication symbol like you added and am still get #value in all the cells. this the order of items I have in my sheet F=date filled
G=Volume
H=Rate
I=Current volume today
J=volume in x days
K=date volume is < 40 liters
 
Upvote 0
How about this?

F​
G​
H​
I​
J​
K​
1​
date filled​
capacity [liters]​
rate [liters/day]​
vol today [liters]​
vol in x days [liters]​
date vol = 40​
2​
04/17/2014​
1,000​
29​
623​
478​
05/20/2014​
3​
4​
F2: InputG2: InputH2: InputI2: =G2 - (TODAY() - F2) * H2J2: =I2 - J7 * H2K2: =F2 + (G2-40) / H2
5​
6​
x [days]​
7​
5​
8​
J6: Input
 
Upvote 0
Thanks for hanging in there helping me but I am still getting #value for all results. I don't have any spaces in the formulas I have entered, should there be some?
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top