Fleet report manager

 

I use a software that is online to track my companies fleet. I am creating reports in excel for things like fuel consumption. I download the spreadsheet from the software and add additional data from other non drivable equipment and then use a pivot table to create the reports with only the info I need for various reports. I try not to change the original columns in any way to keep importing data simple. I need to do this report by “Month” but what I download has a date/ time column where the information is like this:

03/22/2014 8:34 AM EDT

11/24/2014 8:40 AM EDT

I created an additional column “L” labeled “Month” to make filtering the pivottable easier and then created a vlookup equation to autofill the month column. my equation reads this way:

=VLOOKUP(LEFT(E5,2),$Q$27:$R$38,2,)

my problem is unless I do my array column as column to text so that the month number read “1,2,3,4…..” then only the double digit (10,11,12) work correctly but January and the rest do not because the original column has those months as 01, 02,03…. which do not match. so I can’t get my table array numbers to have a 0 in front and work and I can’t change the original column due to the large amount of data…and the idea is to make this fast, easy AND functional. 

Any ideas around this issue short of selecting every day of the month in the pivot table?

By: Ericka

Leave a Reply

Your email address will not be published. Required fields are marked *