Hi Tony,

Can you please upload a sample file, so we can see your data structure?

Dates can be in many unexpected formats, a functional solution can be built only with a sample file.

Catalin

Hi Catalin,

I've attached a small extract to give an idea. The Teaching Week Ranges in Col. I is where I would like to calculate. So with this sample, Row 2 is 10 weeks (2-6, 8-12), Row 3 is 5 weeks, Row 4 is 8 weeksRow 14 is 20 weeks, and so on.

The objective is to be able to take the number of weeks, and multiply by the Duration in Col. F. There are a few other filters and calculations which come into play, but I can handle those easily enough - it is converting the Teaching Week ranges which would be a massive help!

Many thanks for the response,

Tony

Hi Tigger

Give this function a try.

Function skCountWeeks(cell As Range)

Dim WeeksArray() As String
Dim x As Integer
Dim i As Integer
Dim NoOfWeeks As Integer
Dim StartWeek As Integer
Dim EndWeek As Integer

‘Check if a dash exist, this means it is a range of weeks
x = InStr(1, cell, "-")

If x = 0 Then
WeeksArray() = Split(cell, ",")
skCountWeeks = UBound(WeeksArray) + 1
Else
Temp = Replace(cell, "-", ",")
WeeksArray() = Split(Temp, ",")
NoOfWeeks = 0
StartWeek = 0
EndWeek = 0
For i = LBound(WeeksArray) To UBound(WeeksArray)
If Application.IsEven(i) Then
StartWeek = WeeksArray(i)
Else
EndWeek = WeeksArray(i)
NoOfWeeks = NoOfWeeks + EndWeek – StartWeek + 1
End If
Next i
skCountWeeks = NoOfWeeks
End If
End Function

Hi All,

a different approach using a formula.

=Sumproduct(--(Isnumber(Find(" "&Row($1:$30)&","," "&I2&","))))+Sumproduct(Isnumber(Find({"2-6"."8-12"."17-21"."23-27"."2-12"."17-27"},I2))*{5.5.5.5.11.11})

I'm using Excel 2010 (Italian version) and I'm not sure if Excel 2016 could accept "." as delimiters in {5.5.5.5.11.11}

I hope the file attached is clearer than my English.

Vba UDF is by far more elegant.

Regards

Hi all

If the the week range is fixed e.g. 5-day range (you can check to see if any dash exist to determine if it is a range), then count the number of dashes and then multiply by 5 (or whatever day-range)

If no dash exist (assumed to be not a range of weeks) then count the number of commas + 1 (e.g. 4 commas means 5 weeks)

This will only work if the date format entered is consistent and does not have a mixture of 2,3... and 2-6 etc

Sunny Kow

This will also work if there is a combination of weeks e.g. 2,3,4,1-5 etc in the same cell (here it is assumed that the week range is 5 days. Can be modify. Just change the *5 to anything)

=((LEN(I2)-LEN(SUBSTITUTE(I2,"-","")))*5)-(LEN(I2)-LEN(SUBSTITUTE(I2,"-","")))+LEN(I2)-LEN(SUBSTITUTE(I2,",",""))+1

Sunny Kow

Hi everyone, thanks for the replies.

I think there may be some confusion as to week range = 5 days. The object is to work out how many weeks a session is run, rather than the number of days each week (so if a course was booked to run two days a week, this would be two separate rows with e.g. 2 hours each for the period 2-12 giving two bookings of 22 hours in each row). Some events are held at weekends, but these are filtered before working through the data.

I am still very new to VBA stuff. I tried the formula from Canapone, inserting a new column and putting the formula in there. The result is showing the formula, with no number.

I also tried the function by Sunny Kow (Alt+F11, and pasted it into a module). Again, inserting the function into the new column doesn't produce anything.

I'm using Office Professional Plus 2013. I suspect I'm doing something wrong in using them, so any further advice would be very welcome.

Thanks,

Tony.

Hi Tony

Maybe if you could post samples of your expected results in a worksheet, it will help clear the confusion.

Frankly, I am still confused after your latest explanation

Sunny

Hi Sunny,

I've added two extra columns to the original file.Column J shows the desired result from the calculation of the number of weeks, and Column K shows the final calculation multiplying the number of weeks by the duration of the particular teaching session.

The Teaching Week Ranges is based on week number from the start of term at the beginning of the academic year, and based on 3 terms of 11 weeks (so week 1 is the introductory week for new students, and lectures begin on week 2, finishing on week 12, and starting again in week 17 to 27. and again in week 32, which isn't shown in this sample).

So, the number of days in a week is not a factor in this, as each session is listed for a single instance sometime during the relevant week, not a specific day. Hope that makes sense.

The overall objective is to translate the number of hours given over to teaching by a department in a range of rooms (here is it Location WOL128, but the department will teach in several different locations) into the number of hours a room is used by which departments. Therefore, Psychology has used this room for 383 hours over the course of a year, but it may have been used by Physics, Geography, etc. as well. Anything which helps to simplify this process is very, very welcome! So, being able to run a function or formula so that I take a single number into the translation instead of manually working out the number of weeks ... well, I guess you can imagine the help that would be 🙂

Hopefully, this helps to clarify what I am trying to do, all I need now is to be able to apply the helpful stuff which you have all offered 🙂

Thanks again,

Tony

Hi All,

in the attachment I'm using SUMPRODUCT in column J to get Teaching Weeks.

Hope it helps

Brilliant, many thanks, Canapone ... that is exactly what I am looking for! It works superbly on the sample, and also on a single row in the (copy of) the live data ... but quite naturally it is taking a while to run through a few thousand rows to come up with the answers ... maybe I should have just left it running overnight LOL!

Anyway, thank you to everyone who contributed, and once I have this out of the way I will try and analyse what it is doing so I actually understand it as well 🙂

Tony.

(Just finished - around 10 minutes, so not too bad at all ... and that will save me hours of brainwork 🙂 )

Oops! Not quite ... I've found several results which do not come true ... I feared that this was somewhat more complex due to the nature of the data!

No allowance is made for anything in Term 3, Weeks 32-40, so these give a result of zero.

Also, several results which are incorrect as the pattern of weeks is different and/or more complex ...

Examples:

Weeks 3-6, 8-11 should be 8, result is 0

Weeks 4, 18-21 should be 5, result is 1

Weeks 19-20, 24-25 should be 4, result is 0

Weeks 11-12, 19-20, 22, 24-25 should be 7, result is 1

Weeks 17-25, 27 should be 10, result is 1

Weeks 18-21, 23-24, 26-27 should be 8, result is 0

Weeks 2-4, 6, 8-12, 18-21, 23-27 should be 18, result is 11 while 2-4, 6, 8-12, 17-21, 23-27 should be 19 and result is 16

Weeks 2, 4-11 should be 9, result is 10

Weeks 1-12, 17-27 should be 23, result is 11

Hopefully that gives an idea of the variations in the data. Perhaps my original explanation, and the subset of data, didn't reflect the variety as clearly as I should have done 🙁

Apologies,

Tony

Hi Tony

My original codes actually gives your expected result as in column J as per your attachment.

From your latest post to canapone I noticed there are combinations such as 17-25, 27 and this is the problem.

Your original posting did not show such combination. Maybe you could supply all possible combinations and their

expected results as this will make life easier for those who want to help you . Unfortunately my function will only work if there are no such combinations.

BTW I attach the file that I worked on originally for you to have a look. I will not work for your combination though 

I will need to re-look and see what is the best way (if any) to resolve your combo problem.

(Sorry,attached the file twice but don't know how to remove it)

Sunny Kow

Hi Tony

This will will surely work and it can handle any variation 

Sunny Kow