Searching text string and working out elapsed time

[Kevhardy]

Hi,

I have a little experience of Excel but this is a bit beyond my skills.
We are thinking of using Microsoft's StaffHub to do staff rotas but need to generate data for some reports. We can export the data from StaffHub to Excel format but have no control over how the original information is gathered.

The issue I have is that the data arrives in a cell string such as "9:30am - 4pm Training". This represents a shift that started at 09:30 and finished at 16:00. The worker was on training this day but it could also have been 'Hands-on', 'STS', 'A/L' etc.

What I need to do is:
A) look at the string, and work out the start and end times (or at least the elapsed time between the start and end)
B) and what the worker was doing i.e. 'Training', 'A/L', 'STS' etc. as this will decide which column to enter the 'elapsed time' result.

For example:
A1=9:30am - 4pm Training

C1=6.5 (The elapsed time in the column headed 'Training')

Any help/advice gratefully received

Regards

K Hardy

 

[MrTeeny]

A lot depends on how 'regular' the format is as it's easy enough to strip out the individual items using either formulas or VBA and a space as the delimiter

 

[Kevhardy]

The data is fairly regular. I.e. it will always be a start time recorded as 12 hour clock (but could be a.m. or p.m.) followed by an end time followed by the type of shift.
One complication may be that the shifts include a night which starts at 9.3pm and finishes at 7.30am. This caused me some issues before.

 

[MrTeeny]

If it's regular ie start time , hyphen , end time, category just use split() a that'll stick the parts into an array and you can access each part separately

Dim SplitArray() As String
SplitArray = split(Range("A1").Value)

Start time would be in SplitArray(0), end time in SplitArray(2) etc, you could then decide which column to enter the data in from the value in SplitArray(3). You'll probably need to add error coding depending on how regular, fairy regular is.