Formula for time calculation within one cell

I want to put in a formula that calculates the difference between two times within the same cell.

For example the shift 6-2 or 6am-2pm equals 8 hours, and have the total sum of the shifts for two weeks at the end in column "p"

This is a surprisingly complicated problem due to the way Excel stores and manages dates. This solution makes a number of assumptions, specifically:

• times specified do not span more than 24 hours
• am and pm are specified in both start and end dates
• there are no extraneous characters in the shift field
• each day's entry must be calculated independently (no two-week total in one cell)

Take the following example data:

Cell B2 holds the following formula:

=TIME(LEFT(A2,FIND("-",A2)-3)
      +IF(MID(A2,FIND("-",A2)-2,2)="pm",12,0),0,0)

Cell C2 holds the following formula:

=TIME(MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2)-2)
      +IF(RIGHT(A2,2)="pm",12,0),0,0)

Cell D2 holds the following formula:

=IF(C2>B2,C2-B2,C2+1-B2)*24

These cells could theoretically be combined, replacing every reference to B2 and C2 in the final formula with the actual TIME function from each cell.

If the ultimate objective is to sum a series of shifts for a two-week total, I recommend adding an additional row (or column) of calculations for each day individually. The row can be referenced to sum the hours and the row can be hidden from view to keep from disrupting the flow of the document.