Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 11 to 20 of 36

Thread: Date and time

  1. #11


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #12
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    767
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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?

  3. #13
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,601
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by testerxxx View Post
    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.

  4. #14
    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?

  5. #15
    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?

  6. #16
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,601
    Articles
    0
    Excel Version
    O365
    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).

  7. #17
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,601
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by testerxxx View Post
    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

  8. #18
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    767
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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?

  9. #19
    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 by testerxxx; 2014-11-06 at 06:47 PM.

  10. #20
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    767
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.
    Attached Files Attached Files

Page 2 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

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