Having Excel Tally up numeric totals from cells that also contain text and probably more

Brass Contributor



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, 


1 Reply


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.

= 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.