Aloha!

I am a helicopter pilot and am trying to create some sort of running total to implement my own fatigue management system to ensure i'm not overflying myself or what not.

I currently use the attached sheet to log my hours. Column 'F' of the sheet is where I enter how many flight hours for the day and column 'G' totals the hours (cumulative total). I would need to get the running total using the numbers in column 'F' i just can't figure out the formula or if i'll need to create a master sheet or something. This has been mind boggling for the past month and now I'm seeking further assistance.

I know excel but not as well as i'd like and have been having issues trying to come up with a 30-day running or rolling total over various sheets and sometimes workbooks when you get to the end or beginning of the year.

I would greatly appreciate if someone could show or explain to me how to go about doing this.

Attached Files

30 Day Running Total Master Duty Log Shell (OAHU).xls (138.5 KB, 20 views)

When you want to sum a range such as cells G10 to G40 then the formula you need to use is =sum(G10:G40). That is what you are trying to do in G41.

From what your explanation it soundslike you are trying to have the sum of flight hours for the month and for the past 30 days (which should change every day). Is that correct?

hey c991257 - Thank you for the reply. Yes, that is exactly what I need to do. Because it's going back 30 days it will need to pull data from the previous sheet in the workbook. That is the part I am not familiar with. I know how to add data from multiple sheets. I am just not sure how to grab the data from ONLY certain cells to generate the 30 day total properly

ok. I've amended your workbook slightly.

1. I have made the dates dependent on each other (column A). You didn't take leap year into account and you had months that were too long (no such thing as 31-Apr-2016).

2. The dates that don't exist (31-Apr etc) should behidden by hiding the rows (see eg Feb). Normally I would delete the row entirely, but you need to understand where they are. If you delete them (which you can) most formulas will autocorrect to take into account that a row no longer exists.

3. Cell G41 can either be sum(F10:F40) or max(G10:G40). I have opted not to make it = G40, because of 2. above (hidden sums will screw with you).

4. For the "Rolling 30 Day Sum" I have introduced two extra sheets. The "Helpersheet" takes all the dates along with the flight time of the day and sums up the previous 29 days. However for January that is not possible without the "Last Dec" sheet so I have introduced that as well.

5. Next year you need to change cell A10 in Jan to 1-1-2017. That will automatically fix the Month cell that you have on each sheet and it correct the dates on the "Helpersheet".

6. The "Helpersheet" and the "Last Dec" should be hidden.

Additionally

7. In the Example sheet I have changed the format of column C, D & E to HH:MM. If you use numbers like you did you can enter a value of 09:75 which doesnt make sense. The hours have to be entered as 09:00. Entering 0900 will not work. I have not implemented this in the other sheets.

8. In the Example sheet I have deleted rows 8 and 9 where you had entered text. It is a bad idea to enter text over multiple rows when the text ought to be in a single row. What you should do instead is use text wrapping and centering (horizontal and vertical). If you want to force a line change use alt+enter. I have not implemented this in the other sheets.

However

9. Is column F necessary? Isn't it the same as column E?

Play around with the sheet and see if it takes you in the right direction. There is plenty that can be optimised.

/Møller

Attached Files

30 Day Running Total Master Duty Log Shell (OAHU) (2).xls (297.5 KB, 16 views)