Help with Time calculation

[outlandish]

Hello

I have a schedule template using the following format for hours:

6:30a-2p*	OFF	REQ*	7a-3:30p*
2p-10:15p*	2p-10:15p*	2p-10:15p*	OFF
REQ	5a-11a*	5a-11a*	OFF

<colgroup><col span="4"></colgroup><tbody>
</tbody>

what I need to do is for excel to calculate the hour value of each shift.
For example 2p-10:15p is 8.25 hours .

This is what I currently have but it is UGLY.

=IF(OR(B19="REQ*",B19="REQ"),"blank",TIME(MID(B19,FIND("-",B19)+1,LEN(B19)-FIND("-",B19)-2)+IF(RIGHT(B19,3)="p*",12,12),0,0)-TIME(LEFT(B19,FIND("-",B19)-2)+IF(MID(B19,FIND("-",B19)-1,2)="pm",12,0),0,0))

Please advise
Thank you

 

[FranzV]

I agree, it does seem kind of ugly. It is not clear why your data has such a format. Can you change the way your data is stored? If you can, I would suggest the following:

• Input time values without any text, in 24h format, separating hours from minutes with a colon and always including minutes (even if the minutes value is zero).
  • Eg. 6:30, 14:00, 22:15, etc.
  • Excel will detect that you are working with time values and calculations will be very easy.
• Use separate columns for start and end times.
  • This will let you use those values directly in formulas, without any text functions involved.
  • You could add a third column to calculate shift duration ([ShiftEnd]-[ShiftStart]).
• Use cell formatting if you need to display time values in a given format.
  • Eg. "hh:mm" shows 14:30 while "h:mm am/pm" shows 2:30 p.m.

I hope this helps.