Forum Discussion
Having Excel Tally up numeric totals from cells that also contain text and probably more
Hi I am trying to make a spreadsheet for my side job and my hours and expenses and other unique criteria so I have something to present to my boss. One thing I would like it do is tally up total...
You could avoid all of the text conversions and stick with times (fractions of a day) throughout.
Columns I, J, K, M, N become unnecessary. The half hour lunch break simply becomes 1/48 of a day.
= hours - IF(lunch?="y", 1/48, 0)If the "y" is not sufficient, you could use conditional formatting to append the "L" without changing the underlying formula.
Condition
= lunch?="y"
Number format
[h]:mm LThe time is just
= ROUND(adjusted*rate, 2)since both the rate and the time are by the day.