I have attached the excel file to this post. This WORKBOOK has 12 WORKSHEETS - one for every month of the year. This is what we currently use to track Pilot hours.
When I went through it the first time, I added conditional formatting, corrected discrepancies with the way the dates are shown, cell formatting and such but I don't believe the version attached is fully corrected so if you need to feel free to change formulas and/or cell formats. The only thing I prefer not to change would be the structure and layout of the form
That being said Pilots manually enter data on this sheet. This is how they get paid. In Column F, they enter the amount of hours they flew for the day (i.e., 6.2). Column G, is supposed to provide a MONTHLY running total and is conditionally formatted. ***Again, please feel free to adjust the formulas/cell format as necessary***
MY TASK / DILEMMA:
I need to create a 30 DAY RUNNING TOTAL in Column Q. Example, if I am looking at the 'February 2015' Tab, Row 24 (which is February 15) - Column Q should have added the flight hours from that date all the way back to the 'January 2015' tab, Row (which is January 16) which is a full 30 days. I should be getting a total of 89.1
Now, the Pilots usually have a couple of days off in there so I don't know how the zeros or when there are no numbers in the columns affect the true 30 Day total but that's something else to consider?
Also, the cell should be conditionally formatted to flag 100.0 and anything over it as RED.
Any assistance would be appreciated. I feel like i could use a ROUNDUP and/or FILTER formula after converting the cell formats for time and such accordingly and also using a short date instead of 1-31. Along with Helpersheets
Bookmarks