concatenate problem

[Norbert Jaeger]

Hi all,

I do following entries in colum A to F:

A B C D E F
1 14 11 12 0 12 30
2 15 11 5 44 6 23


The entry in row 1 has got the following meaning:
on the 14.11. the machine was stopped from 12:00 to 12:30

The entry in row 2 has got the following meaning:
on the 15.11. the machine was stopped from 05:44 to 06:23

From these entries I would like to get following entries by
concatenating the single cells:


H I J
1 14-11-05 12:00 14-11-05 12:30 00:30
2 15-11-05 05:44 15-11-05 06:23 00:39


thereby the formats have to be dd-mm-yy hh:mm, in order to be able to
deduct I1-H1 and to get the time difference in J1

I have an idea how to solve it, but it seems so awkward that I don't
even try to explain it here. It would just take too long.

Does anybody has an easy way?

Thanks and regards,
Norbert

PS: the entries I do the way as described above because it is much
quicker that way then to have to put in the date and time in that
difficult format, with all the dashes and spaces and colons.

 

[Norman Jones]

Hi Norbert,

Try:

Stop Date/time:

=DATEVALUE(A1&"/" &B1&"/05" &" " & C1&":"& D1)+
TIMEVALUE(A1&"/" &B1&"/05" &" " & C1&":"& D1)

Restart Date/time:

=DATEVALUE(A1&"/" &B1&"/05" &" " & C1&":"& D1)+
TIMEVALUE(A1&"/" &B1&"/05" &" " & E1&":"& F1)

 

[Paul Black]

Hi Norbert,

Just to Add to Normans Excellent Answer, Format the Two Formula Cells
as ...
Format, Cells, Custom, dd/mm/yyyy hh:mm
.... and the Difference in Time as ...
Format, Cells, Custom, hh:mm

Hope this Helps.
All the Best.
Paul