+ Reply to Thread
Results 1 to 7 of 7

Fuel Spreadsheet Date Issue

  1. #1
    Registered User
    Join Date
    07-30-2016
    Location
    Atlanta, GA
    MS-Off Ver
    2011
    Posts
    13

    Fuel Spreadsheet Date Issue

    Hello all,

    I have been working on miscellaneous spreadsheets for a friend of mine who is an independent owner operator truck driver. To say the least, he is extremely thorough and follows every number in his equipments performance and profit margins. This is the first spreadsheet I have put together for him that I have stumped myself. Our goal with the fuel tracking spreadsheet is to be able to input fuel stops as they happen and have the spreadsheet automatically calculate several variables he has chosen to follow along during his trips. There is a sheet for the truck fuel stops, the DEF fuel stops (which is the emissions fuel for newer trucks that is required), and lastly, the reefer unit fuel stops for the trailer's refrigeration units fuel. The problem I am incurring is that when my friend makes two fuel stops in a single day, I am unable to correctly calculate the DEF's burn rate between DEF fuel stops. In using the date as a range variable between fuel stops, the burn rate is calculated correctly until there is two entries of the same date. Then I am unable to figure out how to tell the burn rate formula to only use one of the two same date entries. The basic calculation for the DEF burn rate is: =the DEF gallons purchased at DEF fuel stop / the sum of the truck gallons since the last DEF fuel stop. For example: on 1/12/18 there was a DEF fuel stop where 6.513 gallons of DEF fuel was purchased. Prior to 1/12/18, the last DEF fuel stop was 1/11/18. The total truck fuel gallons purchased between 1/11/18 and 1/12/18 was 158.913 gallons (95.054 on 1/11/18 and 63.859 on 1/12/18. 6.513 / 158.913 = 0.0409 rounded to 4.1%. Here is the actual Excel formula I am using for this equation between the two sheets:

    DEF Burn Rate Formula: =IFERROR(SUM(I3/SUMIFS('5401'!$H$2:$H$3001,'5401'!$B$2:$B$3001,">="&B2,'5401'!$B$2:$B$3001,"<="&B3))," ")

    This formula works great and is accurate until I have two truck fuel stops in the same day. I do not know how to distinguish a variable between the two dates and between to tell the DEF burn rate which fuel stop in that same day to use. Here are two screenshots of both the truck fuel sheet (pictured first) and the DEF fuel sheet (pictured second). Any assistance or help somebody could provide to overcome this hump would be greatly appreciated. I have been working on this spreadsheet for three months and have wrapped my brain around it several times and have not been successful. The Excel Forum was my last resort as I wanted to try and figure this out on my own but I am beginning to believe a fresh set of eyes would help! Please let me know if anymore information about the spreadsheet is needed to help.

    Thanks all!

    Sincerely,

    BaconFanatic2

    TruckFuel.png
    DEFFuel.png

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,011

    Re: Fuel Spreadsheet Date Issue

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    07-30-2016
    Location
    Atlanta, GA
    MS-Off Ver
    2011
    Posts
    13

    Re: Fuel Spreadsheet Date Issue

    I am still struggling to attach my spreadsheet through the provided the steps. Is there another way I can attach it and provide it to be looked at?

  4. #4
    Registered User
    Join Date
    07-30-2016
    Location
    Atlanta, GA
    MS-Off Ver
    2011
    Posts
    13

    Re: Fuel Spreadsheet Date Issue

    Please see attached. Sorry! I finally figured it out
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,011

    Re: Fuel Spreadsheet Date Issue

    Can you tell us where to look specifically? Also we need to know what you are expecting the results to be.

    My initial thought is that you could add a time to the date field.

  6. #6
    Registered User
    Join Date
    07-30-2016
    Location
    Atlanta, GA
    MS-Off Ver
    2011
    Posts
    13

    Re: Fuel Spreadsheet Date Issue

    Good evening,

    What I am looking for is to be able to calculate the DEF burn rate correctly when there are two or more truck fuel stops in the same day. Does that make sense?

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,011

    Re: Fuel Spreadsheet Date Issue

    See point 2 in post #2 - I still can't find your manually mocked up results.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 06-18-2015, 02:09 PM
  2. [SOLVED] Calculating complex Fuel Cost based on Individual Name, Engine CC, Fuel Type and Date.
    By shadypops in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 01:38 PM
  3. [SOLVED] Fuel type fill base on kind of fuel.
    By yukioh in forum Excel General
    Replies: 2
    Last Post: 01-18-2013, 02:12 AM
  4. Issue in the date format in attached spreadsheet
    By sarat47.dash in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-22-2012, 02:37 PM
  5. fuel calculator spreadsheet
    By jltk87 in forum Excel General
    Replies: 4
    Last Post: 04-30-2007, 02:17 PM
  6. Fomula Advice needed for a fuel spreadsheet,
    By Majestic in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-04-2007, 10:19 AM
  7. [SOLVED] how do i set up a spreadsheet to calculate fuel milage
    By Dmds in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-15-2006, 12:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1