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