Thanks:  0
Likes:  0

# Thread: Formulas for tank volume calculation by using DATE

1. ## Formulas for tank volume calculation by using DATE

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

2. ## Re: Formulas for tank volume calculation by using DATE

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

3. ## Re: Formulas for tank volume calculation by using DATE

 E F G H 2 date filled 04/17/2014 F2: Input 3 capacity 1000 liters F3: Input 4 rate 29 liters/day F4: Input 5 current vol 652 liters F5: =F3 - (TODAY() - F2) * F4 6 x 5 days F6: Input 7 volume in x days 507 liters F7: =F3-(TODAY() + F6 - F2)*F4 8 date vol = 40 05/20/2014 F8: =F2 + (F3-40) / F4

4. ## Re: Formulas for tank volume calculation by using DATE

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?

5. ## Re: Formulas for tank volume calculation by using DATE

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?

6. ## Re: Formulas for tank volume calculation by using DATE

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.

7. ## Re: Formulas for tank volume calculation by using DATE

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.

8. ## Re: Formulas for tank volume calculation by using DATE

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

9. ## Re: Formulas for tank volume calculation by using DATE

 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: Input G2: Input H2: Input I2: =G2 - (TODAY() - F2) * H2 J2: =I2 - J7 * H2 K2: =F2 + (G2-40) / H2 5 6 x [days] 7 5 8 J6: Input

10. ## Re: Formulas for tank volume calculation by using DATE

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?

## User Tag List

#### Posting Permissions

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