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 hours from one column, then look at the column for lunch breaks and deduct .5 of an hour or :30 and return a value for days with lunch breaks taken displaying the original total, less the break and then an L next to it so I know it was deducted. The problem I am running into is that any cell that has the L messes up other cells that are using it to calculate other totals. I have tried using =LEFT(A1,4) to make it only add the numeric values but then the returned value has 17 decimal places or it doesn't add right because it's too many characters, etc. I have attached the workbook so you can see how I am trying to set it up. There has to be a smarter way for me to go about this.
FYI I am as dumb as a can of paint thinner, so if you are able, please explain it the same way you would to a walrus or other intellectually equivalent animal and I will do my best to grasp it.
Thank you for your time and attention.
Best regards,
J
- PeterBartholomew1Silver Contributor
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 L
The time is just
= ROUND(adjusted*rate, 2)
since both the rate and the time are by the day.