A working table is on 2nd sheet.
I used Defined Table there. When you enter anything into next row immediately below bottom one of Table, all formulas and formats are expanded to new row (so long as you don't have several different formulas or formats in same column!).
I added fields with formulas which calculates the total of hours and costs visible in Table (FilteredTotal). When p.e. you set the Autofilter for StartTime of Table to March, the total time and cost for March is displayed in those fields.
The column TaskTime and it's total are formatted as "[h]:mm". This format displays hours 24 or more - all other time formats start over at 00:00 whenever the multiple of 24:00 is reached!
As follows from your example data, a task can start on one date, and end on next date (or even several days later?). Because this, the start and end times MUST have date included - otherwise there is no way to calculate the time spent on task properly!
On your original sheet, I wrote a comment why your formula doesn't work!
|