I have a spreadsheet that has dates, the amount of time spent each day and the cost per hour and minute for each amount of time spent working on a project. Kind of like a timer sheet.

I would like to know if there is a better way to:

add the TIME INVOLVED to auto calculate the TOTAL HOURS
Take the TOTAL HOURS and calculate them by 5.555 (333.33 an hour = 5.555 a minute)


Then the number from MINUTE auto-calculates the COST.


Thank you in advance

Attached Images

Screenshot 2022-08-03 092630.jpg (54.0 KB, 9 views)

Attached Files

COST TEMPLATE FOR MS.xlsx (15.5 KB, 5 views)

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!

Attached Files

COST TEMPLATE FOR MS.xlsx (19.0 KB, 6 views)

wow, thank you, love it

I'll play around with it