Register
Log In
Unanswered
Home
June 28, 2016
Offline
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
See Answer
November 8, 2013
Offline
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
June 28, 2016
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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.
Dashboards
June 25, 2016
Offline
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
June 28, 2016
Offline
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
July 3, 2016
Offline
Hi All,
in the attachment I'm using SUMPRODUCT in column J to get Teaching Weeks.
Hope it helps
June 28, 2016
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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: Max Neumann
21 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:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
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
