Time from Text

**Maurice** · 05-25-2005, 04:44 PM

We have a program at work that exports a text field from rotas as 1900-0700 or any variation of. I need to convert this into hours difference and also run across midnight. I've tried "=Right(A1,4)-Left(A1,4)", which gets me partially the way there, but not managed to progress beyond the very basics. I've played with the TIME function to no avail, i managed to achieve what i wanted over nine columns but that is ludicrous, any pointers, flashes of inspiration would be much appreciated. I'm prepared to put this into VBA later, but would rather have it as a calculation on the sheet

**BenjieLop** · 05-25-2005, 05:11 PM

Originally Posted by Maurice

We have a program at work that exports a text field from rotas as 1900-0700 or any variation of. I need to convert this into hours difference and also run across midnight. I've tried "=Right(A1,4)-Left(A1,4)", which gets me partially the way there, but not managed to progress beyond the very basics. I've played with the TIME function to no avail, i managed to achieve what i wanted over nine columns but that is ludicrous, any pointers, flashes of inspiration would be much appreciated. I'm prepared to put this into VBA later, but would rather have it as a calculation on the sheet

ASSUMING that the data being exported is in Cell A1 and that it is always in this form "xxxx-yyyy", try this ...

1. Format, say, cells B1 & C1 as hh:mm

2. In Cell B1, enter this formula : =left(A1,2)&":"&mid(A1,3,2)

3. In Cell C1, enter this formula : =mid(A1,6,2)&":"&right(A1,2)

4. In Cell D1, enter this formula: =((C1-B1)+(B1>C1))*24

There maybe a more elegant solution but this will work.

**Leith Ross** · 05-25-2005, 05:16 PM

Hello Maurice,

This formula will return the hours difference only.

=ABS((VALUE(LEFT(A1, 4)) - VALUE(RIGHT(A1, 4)))/100)

This will works as long as the format is 4 numbers either side of the hyphen.

Sincerely,
Leith Ross