OK, I finally got my work load manageable again, so here goes:
E7 can be simplified to =ROUNDDOWN(C7+D7-A7-B7,0). F7 can be =MOD(D7-B7,1). Your average can be
, and you will have to hit Ctrl+Shift+Enter after you type the formula. If done correctly, curly brackets will appear around the formula.
For the multiple trips in one, you could just have a comments column to track the multiple trips. If the meals is the big issue, that only matters if the last trip is under 12 hours or the first trip is under one day. If neither of these is the case, the number of meals should come out the same even if the trips are on separate lines. You could add in two more columns for final return date and change the formulas for total trip time; if the new columns are E and F, (making the former E and F become G and H, respectively), then G7 would be
and H7 becomes
Now for the fun one: B5 of Yearly Totals could be
This formula assumes that you do indeed add two more columns for the end date; if you don't change K7:K50 to I7:I50. It also assumes that you use the start date as the date of record, rather than the end date (it was easier since there will only be one start date on a trip). This formula can be dragged down through November. For December, you will have to replace the last half with either a manually entered number from next year's sheet or, if you clear out all data at the end of December except for December and then roll forward, B16 can be:
Quick question on the pay halves: what would happen to the pay on Jan 1-3? Or if the month ended on a Monday, what would happen to the last Friday and Monday's pay? Would they carry over into the next month?
Bookmarks