Maurice Wrote:
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)+(B1C1))*24

There maybe a more elegant solution but this will work.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=374055

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=374055

Assuming that the format is consistent, try...

=SUMPRODUCT(MID(A1,{1,3,6,8},2)/{24,1440,-24,-1440})

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=374055

Thank you all,


--
Maurice


------------------------------------------------------------------------
Maurice's Profile: http://www.excelforum.com/member.php...fo&userid=1948
View this thread: http://www.excelforum.com/showthread...hreadid=374055