STEFSAILOR
New Member
- Joined
- Mar 11, 2006
- Messages
- 7
I have a spreadsheet where the begin and end time of an activity are chaotically dispersed
along each line BUT ....within the line however the start time is always first
and the end time comes further in the line somewhere
So at least that part of the input was ok
I have to substract these times to end up with a duration in minutes in the next collumn
Is there a way to get those begin times and end times neatly under a "begintime" collumn
and the end times under an "endtime" collumn again
and then do the substraction with a result in the next collumn
in minutes, without disturbing the sequence of the rest of the data in the line ?
I do not understand the HTLM "mumbo Jumbo" so i'll try to show my sheet here in plain text an the result I want to obtain hope this is understandable
I tried this with concatenation of different formula's but I think this needs VBA
and I do'nt know much about that...
I HAVE NOW
o A/B/C/D/E/F/G/H/I/J etc up to 15 collumns with data
1 john/black/toy/10:30/green/16:34/water/car/Knife/grey
2 Mike/www/ /task/ /yellow/9:30/red/12:56/ black
3 Sid/ /Ski / /15:21 / / /22:45/Brown/Nails
down to 6530 lines in the same chaos....!!!
I WOULD LIKE
0 A / B / C / D / E / F / G/ H/START/END/MIN'
1 john / Black / Toy /green /water /car/knife/grey/10:30/16:34/36',00
2 Mike/www/ /Task/ /yellow/red/black/9:30/12:56/206,00
3 Sid/ /Ski/ / / /Brown /Nails/15:21/22:45/ 444,00
this is probably a tough job.
I tried to go trough the whole questions part of the forum to see if a similar VBA code could be adapted to my problem but no clue ...
I tried to make a vba with the macro recorder but that is only for simpeler tasks...
The recorder fails to identify the "time"character when displacing those data to other collumns...and blindly displaces regardless of the contend
I am pretty desperate and a bit reluctant to go over all the 6580 lines of the sheet and do the job manually...
Thanks for any idea's or help...
stef
along each line BUT ....within the line however the start time is always first
and the end time comes further in the line somewhere
So at least that part of the input was ok
I have to substract these times to end up with a duration in minutes in the next collumn
Is there a way to get those begin times and end times neatly under a "begintime" collumn
and the end times under an "endtime" collumn again
and then do the substraction with a result in the next collumn
in minutes, without disturbing the sequence of the rest of the data in the line ?
I do not understand the HTLM "mumbo Jumbo" so i'll try to show my sheet here in plain text an the result I want to obtain hope this is understandable
I tried this with concatenation of different formula's but I think this needs VBA
and I do'nt know much about that...
I HAVE NOW
o A/B/C/D/E/F/G/H/I/J etc up to 15 collumns with data
1 john/black/toy/10:30/green/16:34/water/car/Knife/grey
2 Mike/www/ /task/ /yellow/9:30/red/12:56/ black
3 Sid/ /Ski / /15:21 / / /22:45/Brown/Nails
down to 6530 lines in the same chaos....!!!
I WOULD LIKE
0 A / B / C / D / E / F / G/ H/START/END/MIN'
1 john / Black / Toy /green /water /car/knife/grey/10:30/16:34/36',00
2 Mike/www/ /Task/ /yellow/red/black/9:30/12:56/206,00
3 Sid/ /Ski/ / / /Brown /Nails/15:21/22:45/ 444,00
this is probably a tough job.
I tried to go trough the whole questions part of the forum to see if a similar VBA code could be adapted to my problem but no clue ...
I tried to make a vba with the macro recorder but that is only for simpeler tasks...
The recorder fails to identify the "time"character when displacing those data to other collumns...and blindly displaces regardless of the contend
I am pretty desperate and a bit reluctant to go over all the 6580 lines of the sheet and do the job manually...
Thanks for any idea's or help...
stef
