How to give Numeric Values to text

I am making a schedule and i would like the times i have set to the right of the chart to be worth the total amount of hours (I.e. 10A-6P = 8) I have attached the chart i am working on but cant seem to figure out how to do this. I want the total hours to be automatically counted and listed in the last right column.

Can anyone else me with this?

What will be entered in each day's cell entry? e.g. will D12 say "10A-6P"? Is each row (12 to 34) going to be a different person?

Originally Posted by cantosh

What will be entered in each day's cell entry? e.g. will D12 say "10A-6P"? Is each row (12 to 34) going to be a different person?

I would like each day to have a scheduled time which you can see yo the right of the spreadsheet. Those times I would like to have a valued based on the time. So 8a-6p is 8 hours I would like the time to show but in the total hours column I want that to add it up based on the scheduled times

FDibbons is right about restructuring things. Your best bet is to adjust things so that you're entering numbers. However, if you really need to enter the shifts into columns D through J as they're currently constituted, then you should at least enter the number of hours for each shift in N19:N24, which would then allow you to do a straightforward sum of VLOOKUPs using the following formula in K12 (then fill down):

Formula:

Please Login or Register  to view this content.

You can set up Helper columns off to the right with the formulas that change text to values and do the math.

Not elegant but, it works.

See the attached workbook