Forum Discussion

johnsboxftm's avatar
johnsboxftm
Brass Contributor
Oct 13, 2023

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

  • johnsboxftm 

    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.

Resources