Date and time

testerxxx

New member
Joined
Oct 30, 2014
Messages
39
Reaction score
0
Points
0
that.

First off Thank you for your help, no matter how much or little.

Let me tell you what my project is about in a nut shell. I take measurements from 15 water tanks every time I pull water out. The measurement is in feet and inches. The flow/fill rate in each tank is different and that rate in every tank always fluctuates so I am looking for a formula that can adjust with that fluctuation. I pull 4 or 5 feet of water at one time from each tank.

I want my spreadsheet to calculate the flow/fill rate based on the measurements from the previous pull to the pull I'm about to do. ie...I pulled the tank down to 1'6" at 12:00 pm 10/31/14. Now the tank is 5'6" at 2:00 pm 11/01/14. So the tank filled 4' in 14 hrs. I know how to do the math on paper to figure out the flow/fill rate. My problem is working with dates and times in excel. I've figured out now() and today() but I'm baffled on putting a static current time and date in a cell, something that I'm thinking should be easy.

This is what I have working so far. I have a formula that converts ie. 2'3" into 27 (inches). I have a formula that subtracts 4' or 5' from the measurement I take before I pull a load; what is remaining in the tank. This uses a cell that I put how much I will pull and the cell that contains the ' " converted into inches (the 27 in this ie). I have it layed out to where there are 2 column sections, one will have the previous date/time and measurements and the other is where I put the new info. That means every time I will put the new info in where the previous info was. I'm guessing that might be problematic with the formulas.

So here are 3 things that I would like help with. They are listed in order of importance. First, what formula will give me the number of hours between the previous date/time I took measurements and the current date/time I'm taking them. Will I have to enter the date/time in a specific format? That question leads into the second thing I need help with. How can I have excel automatically put the current date/time in a specific cell when I put my first measurement into a different cell?
And the last thing I need help with is more of a curiosity question. Is there a way for Excel to keep a log of all the previous flow/fill rate and use that info over time to get a more accurate over time.

Thank you for taking the time to read through my problem. It was a long read and I'm sorry for that. Extra Thanks to those of you who helped.
 
It would be better if you could post a workbook with some sample data and a few worked examples, should be straight-forward then.
 
Yeah I prob should have in the first place. I'm looking for a place to upload the xls now. Also let me say I'm doing all this from my phone and I'm using polaris office for the spreadsheet. So vba or anything outside of formulas as far as I know would be unusable.
 
here it is I hope

It would be better if you could post a workbook with some sample data and a few worked examples, should be straight-forward then.

Here is the file I'm working on. Very crude because I for blocked by date and time.

www 4shared com

/file/EqdQvXyiba/North_oil_field_3.html

I hope that works.
 
Sorry for how crude that link is. I'm new here and don't yet have the 5 posts required to be able to put a link in a post.
 
Wow do not go to that link. 4shared is awful. They just want to sell stuff. I'm so sorry for this. I'm new at it. Please forgive me. I'll look for another place.
 
Why not post your workbook here?
When replying to thread, use the go advanced button then scroll down and click manage attachments.
 
the link finally

Ok I found it. I had to go to fill site from mobile version. The manage attachment option was not in mobile site. But for what is worth here it is.

OK well I uploaded the files to here but I cam drag and drop them worth the browser app on my phone. I hope the link in my other post works
 

Attachments

  • North oil field 3.xlsx
    14.9 KB · Views: 15
  • North oil field 2.xlsx
    14.8 KB · Views: 24
Can you explain how you use this spreadsheet?
What you enter, where, when and why?
What you expect as a result of what you enter, where and why you expect it?
Where and what would be entered next pull and where and what you expect to happen then?

As for a static date and time, seems the only way without VBA would be to enter this in the cell
Ctrl + ; then a Space then Ctrl + Shift + ;
 
Thank you for your patience. I work long hours and try to get back here asap.

I've decided to get a laptop pc for this spreadsheet and other things. So vba is on the table. If that is the easiest and only way to automate the date cell them let's do that.

I've attached the xlsx again with the only change being a new column between the colored cells to make it easier to see while I explain what I had in mind. Please feel free to shoot down anything I say and feel free to suggest the best way to do this because I have limited knowledge of excel.

What I want this spreadsheet to do...Give me more accurate water levels of the tanks I pull from. I'm hoping I will be able to see a real time level of each tank. So when I look at the spreadsheet at 2pm and see the level is 5', I can check it again at 3pm and see the estimated level. I also would like it to instantly give me the time and date when it will be ready for me to pull another load. I pull from 30 tanks, 2 at a time. I want to know when to go to each pair of tanks when they will have a full load for me to pull.

The water in my tanker is measured by barrels. My tanker holds 160 barrels (bbls). The water in the tanks is measured by feet ' and inches ". 20 bbls equals 1'. Therefore my tanker holds 4'. As of right now I have a formula that allows me to change the number of barrels I tale incase I didn't estimate the right time to get a full load and I have to take less. That's one of the things in hoping to avoid with this spreadsheet, but I would like that option to stay just in case.


Each pair of tanks fills at the same time from the same place so virtually at the same rate. Their levels are almost always the same, ie: 5'4"/5'6". But each of the 15 pairs fill at different rates and are always at different levels.

Now let me explain what I have done so far.

For whatever reason I've been putting all the formulas in the bottom oil pad. The Merlyn Olson, rows 44-46.

Column a,b and c is the name and distance I travel to each oil pad, where each of the 15 pair of water tanks are.

Column d is where I have the variable for number of barrels I actually take. Cell d44 for example is called up in the formula in cell j45.

Columns e44 and f44, in green. That is where I put the water tank levels before I pull my load. Each cell representing each tank.

E45 and f45, in red is the level of the tank after I pull my load. The formula in j45 would go in those cells. Again with d44 being a variable in the formula in case I had to pull less than a full load.

E45,f46 (merged), in yellow is the time and date I pulled my load. Since I pull from both talks at the same time I only need to show the time/date once.

The next set of colored cells would be the exact same info and formulas. Each set would be current pull info and previous pull info. Now this is where my lack of excel knowledge kicks in. Every time I add info I would put it in the previous info cells. That would make the previous current and what was current previous. My concern worth that is well it effect the formula for finding the hours between the previous and current pulls? Because the dates will switch cells. Does the previous date always have to be subtracted from the current? So if e46 is current and h46 is previous the formula would be like =e46 - h46. Then when new info is added h46 becomes current and e46 is previous and the formula has to be =h46 - e46?

Cell j44 is the formula that let's me use the format 5'4". It removes the ' and " symbols and changes it to inches only. This will be needed in the flow rate formula.

Cell j45 had the formula that subtracts the variable in d44 (number of barrels I actually pulled) and puts the inch value back to 5'4" format. That is the formula that would be in all the red cells.

That's everything in there worth anything. I hope this past helps. I hope I have given enough info and not to much.

I will await your, or anyone's, reply.
 

Attachments

  • North oil field 4.xlsx
    15.6 KB · Views: 8
Hello tester,

Good choice to go with laptop pc.

If I follow this correctly you only enter 6 things:
tank1 before pull level
tank1 after pull level
tank2 before pull level
tank2 after pull level
barrels pulled
and want to have the pull date/time put in automatically

If this were my project, each tank site would have a sheet of its own and I'd use a UserForm for consistent entries.

Column A would be the pull date/time and everything associated with that pull would be in that same row, including all your calculations and formulas.
Every new pull would go in the next row down. This would give you the "log" you mention in the original post and formulas could just be dragged down.

More questions

The 160 barrels that you pull, or some other amount that you will enter, is that a metered amount or calculated from the before and after levels?

Your tanker holds 160 barrels which is 4 feet, is that 4 feet from one tank, 4 feet from each tank or 2 feet from each tank?

What is the highest level of any of the tanks? (I'm thinking drop down combo boxes for easy entry)

Do you have any actual past data we can use to test suggestions we come up with?
 
My concern worth that is well it effect the formula for finding the hours between the previous and current pulls? Because the dates will switch cells.

You could take the absolute vcalue of the difference, then it would not matter which is current and which is previous

=ABS(E46-H46)

If you format the cell as [h]:mm, that will take care of periods longer than 1 day.
 
With what I have right now I have enter 3 things and sometimes 4.

Tank 1 before pull level
tank 2 before pull level
the date And time, yes which I'm hoping to have auto added when I put in the before pull level for tank 1.

Can't all the calculations be done by changes in one cell then another then another and so on? I already have a formula that auto calculates the after pull levels.

I'm also not familiar with UserForms.

And as it is now just guessing when the tanks have enough to pull a full load, if they are short I do have to change the barrels pulled.

I'm wanting to see all the info for every pair of tanks on one page so I don't have to flip through 15 sheets to know what needs pulled. That would be OK if I can all least get the estimated full pull time for each pair on one page. I do see how your idea keeps the log and will easily give me a better flow rate.

More Answers:

The 160 barrels is the max my tanker can hold. That is the goal of this spreadsheet, that I will always pull 160 bbls, a full load. The pull amount, with the tanker i have, would only ever be 120 or 160. In any tanker it would be a waste of time and money to pull less than 120 bbls (6', 3'from each tank). There is however a tanker that holds 200 bbls (10', 5' from each tank). I don't currently use it but might some day. It shouldn't affect any formulas though, but if there will be any drop downs with barrels pulled it prob should go to 200.

20 bbls (barrels) = 1' so 160 bbls = 8', that's 4' from each tank. Or if I pull 120 bbls it's 6' total, 3'from each tank.

The tanks actually hold 20'. The goal is to keep them as empty as possible all the time. Because of where the drain valve is on them they will always have 1'2" even after I've pulled out as much as I can. The highest actual water level I've seen is 8'11". I also have to take the measurement in feet and inches, even if it's 7'0".

As far as actual data you can just make it up. Just a few parameters...the water level for each pair of tanks is always within 6", but more typically within 1-3". ie 5'2"/5'3", 6'5"/6'8", 5'11"/6'1". The 15 pair of tanks are rarely the same level. If they are it's only by chance. The same with their fill rate. While the pairs get filled from the same oil well at the same time and basically have identical fill rates, the 15 pair all have different fill rates.

One last thing for this post. Something I would LOVE to see if possible. A real time up to date tank level. Just a cell that, idk, maybe uses the now( ) function and updates the actual estimated tank level every 15 mins. Or even make that a variable you can set to 15 or 30 or 60 mins. Just a thought. Would be awesome. That could be a stay on each individual oil pad sheet. What do you think?
 
I understand the abs part but is it really as easy as subtracting one from the other? I'm guessing they would have to at least be in the same format?
 
Yes I forgot to mention that, you need to enter those time/dates as real date times not as text (you can always format it to look the way that you want).
 
One last thing for this post. Something I would LOVE to see if possible. A real time up to date tank level. Just a cell that, idk, maybe uses the now( ) function and updates the actual estimated tank level every 15 mins. Or even make that a variable you can set to 15 or 30 or 60 mins. Just a thought. Would be awesome. That could be a stay on each individual oil pad sheet. What do you think?

You could use an Ontime formula, kick it off in the workbook open event

Code:
NextTime = Now() + TimeSerial(0,15,0)
Application.OnTime NextTime,"UpdateTankTime"

and in the woirkbook close event, stop it

Code:
Application.OnTime NextTime,"UpdateTankTime",,False

and in a standard modiule have the code

Code:
Public NextTime As Double

Public Sub UpdateTankTime

Worksheets("Sheet1").Range("H51").Value = Format(Now(),"hh:mm mm/dd/yyyy")
End Sub
 
tester,
I understand what you're after with this spreadsheet and it only makes sense to have something more accurate than a guess, but I still don't understand the barrels pulled amount. If you go out expecting to pull 160 barrels and there is only 150 barrels available, how much do you pull? and how do you know how much to enter in the spreadsheet?
 
20 barrels = 1'
I only pull by the foot. No half foot.
Each tank in the pair gets the same amount pulled, therefore I pull everything in 2s. So 6' (120 bbls 3' from each tank), 8' (160 bbls), 10' (200 bbls). I started at 6' because that is the bare minimum I would pull. So if 150 bbls were available to pull (that not including the 1'2" I told you can't be pulled), that would be 150 ÷ 2 (tanks) = 75 (bbls in each tank) ÷ 20 (bbls/foot) = 3 (') remainder 15 (round down and disguard).

Answer. 3' or 120 bbls.

But I would only enter the measurement before the pull and change the bbls pulled only if I could only pull 120, otherwise the variable for that formula would remain 160, the goal for this spreadsheet.
 
Last edited:
Hello tester

Bin messing around with your workbook and while it's not a formula solution and is multi sheet, you might be interested in having a look, if not, doesn't matter, I enjoyed playing around with it.

Here's what I did.
- commented out your original cells E49 and E51 to eliminate a circular reference error that was coming up.
- added a generic tank sheet set up for data entry and, provided I followed the explanation of thinks, the formulas to calculate everything that could be needed. The formulas were dragged down to about row 50.
- used a macro to create a sheet for each tank site from the names you have in your column A and copied a portion of the generic sheet to each. This is why entries are all the same.
- added a userform for the data entry. Doesn't have much for error checking.
- added a summary sheet

have a look and let me know if this makes any sense to you.

Good luck with your project.
 

Attachments

  • WaterTankLevels.xlsm
    188 KB · Views: 21
Back
Top