I'm setting up several thousand contract names for a workforce management
program from time-formatted data in Excel and am hoping there might be a way
using a VB script to look up the value of a cell and convert it to form part
of the unique name given to that contract.

Example: the data running from Sunday start attendance to Saturday end
attendance is contained in, say, cells: c110-p110

00:00 00:00 08:00 16:00 08:00 16:30 08:00 16:30 08:00 16:00 08:00 16:00 00:00 00:00

The standard part of the contract name I need to generate is: "FT NR 36",
followed by:

M (8h-16h),Tu (8h-16h30), W (8h-16h30), Th (8h-16h), F (8h-16h)

The 00:00 values in start end of first 2 cells (Sunday) c110 & d110 and last
2 cells (Saturday) o110 & p110 should return no values in the heading, as
shown above. If there was data in these cells it should return S(start-end
hrs) and Sa (start-end hrs) in the same format as the Mon-Fri attendances
shown above.

Many thanks for any advice

Try this code

Sub contractname()

DNames = Array("M", "Tu", "W", "Th", "F")
RowNum = 110
Contract = "FT NR 36"
CName = Contract
DayIndex = 0
For ColCount = Range("C" & RowNum).Column To _
Range("P" & RowNum).Column Step 2

StartHour = Hour(Cells(RowNum, ColCount))
StartMinute = Minute(Cells(RowNum, ColCount))
EndHour = Hour(Cells(RowNum, ColCount + 1))
EndMinute = Minute(Cells(RowNum, ColCount + 1))

CHours = DNames(DayIndex) & " (" & StartHour & "H"
If StartMinute < 0 Then
CHours = CHours & Format(StartMinute, "#00")
End If

CHours = CHours & "-" & Format(EndHour & "H", "#00")
If EndMinute < 0 Then
CHours = CHours & EndMinute
End If
CHours = CHours & ")"

CName = CName & "," & CHours
DayIndex = DayIndex + 1
Next ColCount

End Sub


"Zakynthos" wrote:

I'm setting up several thousand contract names for a workforce management
program from time-formatted data in Excel and am hoping there might be a way
using a VB script to look up the value of a cell and convert it to form part
of the unique name given to that contract.

Example: the data running from Sunday start attendance to Saturday end
attendance is contained in, say, cells: c110-p110

00:00 00:00 08:00 16:00 08:00 16:30 08:00 16:30 08:00 16:00 08:00 16:00 00:00 00:00

The standard part of the contract name I need to generate is: "FT NR 36",
followed by:

M (8h-16h),Tu (8h-16h30), W (8h-16h30), Th (8h-16h), F (8h-16h)

The 00:00 values in start end of first 2 cells (Sunday) c110 & d110 and last
2 cells (Saturday) o110 & p110 should return no values in the heading, as
shown above. If there was data in these cells it should return S(start-end
hrs) and Sa (start-end hrs) in the same format as the Mon-Fri attendances
shown above.

Many thanks for any advice

Joel,

That's great - thank you! - I'll certainly give it a try.

best wishes,

"Zakynthos"

"Joel" wrote:

Try this code

Sub contractname()

DNames = Array("M", "Tu", "W", "Th", "F")
RowNum = 110
Contract = "FT NR 36"
CName = Contract
DayIndex = 0
For ColCount = Range("C" & RowNum).Column To _
Range("P" & RowNum).Column Step 2

StartHour = Hour(Cells(RowNum, ColCount))
StartMinute = Minute(Cells(RowNum, ColCount))
EndHour = Hour(Cells(RowNum, ColCount + 1))
EndMinute = Minute(Cells(RowNum, ColCount + 1))

CHours = DNames(DayIndex) & " (" & StartHour & "H"
If StartMinute < 0 Then
CHours = CHours & Format(StartMinute, "#00")
End If

CHours = CHours & "-" & Format(EndHour & "H", "#00")
If EndMinute < 0 Then
CHours = CHours & EndMinute
End If
CHours = CHours & ")"

CName = CName & "," & CHours
DayIndex = DayIndex + 1
Next ColCount

End Sub


"Zakynthos" wrote:

I'm setting up several thousand contract names for a workforce management
program from time-formatted data in Excel and am hoping there might be a way
using a VB script to look up the value of a cell and convert it to form part
of the unique name given to that contract.

Example: the data running from Sunday start attendance to Saturday end
attendance is contained in, say, cells: c110-p110

00:00 00:00 08:00 16:00 08:00 16:30 08:00 16:30 08:00 16:00 08:00 16:00 00:00 00:00

The standard part of the contract name I need to generate is: "FT NR 36",
followed by:

M (8h-16h),Tu (8h-16h30), W (8h-16h30), Th (8h-16h), F (8h-16h)

The 00:00 values in start end of first 2 cells (Sunday) c110 & d110 and last
2 cells (Saturday) o110 & p110 should return no values in the heading, as
shown above. If there was data in these cells it should return S(start-end
hrs) and Sa (start-end hrs) in the same format as the Mon-Fri attendances
shown above.

Many thanks for any advice