Excel Help Please - Time Difference Calculation

Delboy24

Hi,

I have a works spreadsheet given to me which for example has time worked in cell A1 - 06:00-15:00

In cell B2 I have to put in manually the difference in hours to show actual time worked.

I have said having a start/finish/worked format would work to enable automatic calculation of the time difference but I have been told I cannot alter in any way the layout but am welcome to put formulas into cells if it helps!!!.....go figure!

Is there anyway in excel to show the difference in a work pattern shift when all the data is only in one cell in a time format as above?

Thanks

Stompa

You could use something like:

=TIMEVALUE(RIGHT(A1,5))-TIMEVALUE(LEFT(A1,5))

though it'll only work as it stands if they've stuck rigidly to the format 'hh:mm-hh:mm' (i.e. no spurious spaces or whatever). It'll also fail if the second time is less than the first (e.g. 20:00-02:00). It could of course be tweaked to cover those possibilities if need be.

Mattjimf

I really don't think there is a way to do what your wanting. I stand corrected.
Why not just copy the spreadsheet (saving it with a different name), make it the way you want it, then copy the row/column onto the original spreadsheet.

Stompa

Thinking about it, the spurious spaces issue would be easily solved by using:

=TIMEVALUE(RIGHT(TRIM(A1),5))-TIMEVALUE(LEFT(TRIM(A1),5))

and the start and end times being on different days by using:

=MOD(TIMEVALUE(RIGHT(TRIM(A1),5))-TIMEVALUE(LEFT(TRIM(A1),5))+1,1)