June 28, 2016

Hi everyone.

I have a regular task, where I have to calculate the total number of hours in relation to a given range of weeks. The number of weeks may be 'simple' e.g. 2-12 is 11 weeks, so a 2-hour session for 11 weeks is 22.

However, the weeks may also be e.g. 2-12, 17-27 (so 22 weeks). More complicated still, I could have 2-7,9-12 or 2, 4, 6, 8, 10, and other similar combinations.

If I could calculate the total number of weeks by a formula or something, this would be an immense time saver instead of having to work out the weeks, and multiply by the hours, to transfer into another sheet. It would also stop me wanting to do something exciting like watching paint drying!!! 🙂

Any help or ideas much appreciated.

Thanks,

Tony

June 28, 2016

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

Dashboards

June 25, 2016

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

July 3, 2016

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

Dashboards

June 25, 2016

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

Dashboards

June 25, 2016

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

June 28, 2016

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.

June 28, 2016

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

June 28, 2016

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 🙂 )

June 28, 2016

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

Dashboards

June 25, 2016

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

Dashboards

June 25, 2016

Hi Tony

This will will surely work and it can handle any variation

Dim WeeksArray() As String

Dim i As Integer

Dim NoOfWeeks As Integer

NoOfWeeks = 0

WeeksArray() = Split(MyRange, ",")

For i = LBound(WeeksArray) To UBound(WeeksArray)

If InStr(WeeksArray(i), "-") Then

NoOfWeeks = NoOfWeeks + Abs(Evaluate(WeeksArray(i))) + 1

Else

NoOfWeeks = NoOfWeeks + 1

End If

Next i

skCountWeeks = NoOfWeeks

End Function

Sunny Kow

Answers Post

July 3, 2016

Hi Tigger, hi Tony,

first of all, Vba offers far better solutions (see Tony's user defined function)

Just for fun I've integrated the former formula in order to accomplish new duties as assigned.

It's not very elegant. I've underline the patch.

=SUMPRODUCT(--(ISNUMBER(FIND(" "&ROW($1:$50)&","," "&I2&","))))+SUMPRODUCT(ISNUMBER(FIND({"2-6","8-12","17-21","23-27","2-12","17-27","3-6","8-11","18-21","19-20","24-25","11-12","17-25","23-24","26-27","2-4","4-11","1-12"},I2))*{5,5,5,5,11,11,4,4,4,2,2,2,9,2,2,3,8,12})

So if you'd need to add 1-100:

=SUMPRODUCT(--(ISNUMBER(FIND(" "&ROW($1:$50)&","," "&I2&","))))+SUMPRODUCT(ISNUMBER(FIND({"2-6","8-12","17-21","23-27","2-12","17-27","3-6","8-11","18-21","19-20","24-25","11-12","17-25","23-24","26-27","2-4","4-11","1-12","1-100"},I2))*{5,5,5,5,11,11,4,4,4,2,2,2,9,2,2,3,8,12,100})

Tony's udf handles automatically assigned duties and it's more robust.

Cheers

June 28, 2016

Many thanks, Sunny - I've tested on a copy of live data, and all looks great, including week 1, weeks in term 3, and the various oddball combinations 🙂 If you are ever in London, let me know - lunch is on me for the time saved 🙂

Thanks also to everyone who offered thoughts on this - it is all much appreciated. I should have some extra time available now to try and understand what the code does!

Have a great day,

Tony.

Most Users Ever Online: 57

Currently Online:

1 Guest(s)

Currently Browsing this Page:

1 Guest(s)

Top Posters:

SunnyKow: 651

Frans Visser: 210

David_Ng: 96

mey tithveasna: 71

A.Maurizio: 60

rathanak: 58

yhooithin05: 54

Anders Sehlstedt: 47

julian: 46

PaulFogel: 37

Newest Members:

Hubert Brown

KATE HUDSON

hugo guerrero

Bertrand Taylor

Bertrand Taylor

Toni Fischer

jamie jaco

Dante Perez

Robert Koeneman

Thomas Kreimer

Forum Stats:

Groups: 2

Forums: 18

Topics: 935

Posts: 4405

Member Stats:

Guest Posters: 1

Members: 42350

Moderators: 1

Admins: 3

Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea

Moderators: Genevieve Tupas