• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conversion of Date slots into Dashboard (Bookings of event house)

R

ravikiran

Member
Hi,


I am trying to develop an excel application for an Event house.


They have two houses and each will have business slots booked throughout the year. These slots will be in hours. They are open for 8 hours every day.

Slots are booked in the following way.

5th April to 7th April for 20 hrs, means

8 hrs on 5th April

8 hrs on 6th April and

4 hrs on 7th April


I want to have a forecast for the next 1 week in the following way: if fully booked in Red, Fully available in Green and Partly available in Pink with hours available


https://hotfile.com/dl/201536385/6a27036/Dashboard.png.html


Can I have get some help in getting this done using VBA? Or is it possible to get this done with Formulas? Guidance will be of a lot help.


Sample file attached.


https://hotfile.com/dl/201534122/8a3c14e/Allocation_Template.xlsx.html


Thanks in advance,


regards,

Ravi.
 
Hi Ravi ,


You will certainly get help from members of this forum , but I would like you to bear in mind that file-sharing sites such as Hotfile impose a download limit of one file per 30 minutes , which means for someone to access your two files , they either have to have an account , or they have to wait till they are allowed to download the second file.


There are so many other file sharing websites which do not impose any limits ; there are many others which do not even require you to have an account for uploading a file ; it would be nice if you could use such websites ; one such is SpeedyShare ( http://www.speedyshare.com/upload_page.php ).


Narayan
 
Thank Narayan. I found that I can use Hotfile through the sticky post.


I uploaded the file to Allocation Template


I didn't understand how to edit my previous post. So re-posted the link above.


I'll be very glad to crack this issue. I am banging around it for a while.


Thanks,


Ravi.
 
Hi Ravi..


Can you please check below POST..

try to play with it.. and adapt it accordingly..


http://www.get-digital-help.com/2013/03/05/heat-map-calendar/


we are here.. if you need any help.. :)


Regards,

Deb
 
Hi Gurus,


Sorry for my delay in getting back.


I did try the VBA approach suggested by Debraj, but it seems like getting complicated.


Is there a way to achieve this using some complex formula :) I am sure it will work with a good array formula. But I am an illiterate in the area of "Arrays".


Please help.


Link to sample File: http://speedy.sh/EEdx9/Allocation-Template.xlsx


Thanks in advance,


Ravi.
 
Hi Ravi ,


I am not able to understand your requirement ; do you need the columns C , D and E to be filled up based on what has been entered in the Dashboard ( J5:p6 ) , or do you need the Dashboard to be filled up based on what has been entered in columns C , D and E ?


If it is the latter , and you have already done it for the data which is entered in columns C , D and E , then the dates in column C and D are different from the dates in J4:p4 ; can you clarify what is the output , and how it is to be derived from the input ?


Narayan
 
Hi Ravikiran,


To the best op my knowledge it is not possible to create a 'complex' formula with the requirements u have. Though I may be still an amateur in VBA, try this:

[pre]
Code: 
Dim AmDays, AveHrs As Integer
Dim Row As Long

With ActiveSheet

For Row = 4 To 16 Step 1
If .Cells(Row, 5).Value <> "" Or 0 Then
AmDays = .Cells(Row, 4).Value - .Cells(Row, 3).Value + 1
AveHrs = .Cells(Row, 5).Value / AmDays

.Cells(Row, 6).Value = AveHrs
Select Case .Cells(Row, 6).Value
Case Is >= 8
.Cells(Row, 6).Interior.Color = RGB(255, 0, 0)

Case Is < 8
.Cells(Row, 6).Interior.Color = RGB(255, 0, 130)

End Select

Else
.Cells(Row, 6).Interior.Color = RGB(0, 255, 0)
End If
Next Row
[/pre]

Hope this helps! Its based on the lay-out you put online


Krist
 
Hi Narayan,


Thanks for pointing the mistake. Silly me, the dates are mixed up, and I am wondering why I am not getting any response from the experts. Obviously it didn't make any sense.


I updated my sample workbook in this attachment: http://speedy.sh/qqqK2/Allocation-Template.xlsx


Yes, the data is filled in the table, as to what is the Start Date and what is the end date for each booking. Lets say, House-1 is booked for 20 hrs, from 5-Apr to 7-Apr. It is split as 8 hours each day. So 8 hrs on 5th, 8 hrs on 6th and 4 hrs on 7th. That makes, the House-1 unavailable on 5th and 6th, and partly available for 4 hrs on 7th.


So 5th and 6th will be coded in Red and 7th will be coded in Pink (with available hours). lets say, if another customer makes a booking for 4 hrs on 7th, it'll be coded as Red (unavailable). This scenario is displayed in Sheet2.


To make things complex, we can't have the list in an order. The house can be booked for 15th first and then for 13th to 14th. So when we get a call from a customer, it is becoming very irksome for the administrator to go through the complete list and confirm if the house is available/partly available/unavailable on the requested date.


This dashboard will solve that problem by the color coding. That's what my business requirement is. I'll be moving the dashboard to a separate sheet and make it look much better :) (from chandoo's training ofcourse).


Thanks for looking into the issue. Let me know if any more clarification is need.


cheers,

Ravi.
 
Thank you Krist. I'll try your code. Honestly, either VBA or Formulae solution suits me fine. :)


I made a mistake and uploaded the wrong sample workbook. I detailed my requirements once again in my last post. I'll try and let you know the result.


cheers,

Ravi.
 
Hi Ravi ,


Thanks for the clarification ; in case you don't get an answer from the others in this forum , I'll post one tomorrow morning , since it's late tonight.


Narayan
 
Thanks for the help Narayan. I'll wait for your solution. I'am trying to modify Krist's macro. If I am able to crack, I'll post it for sure.


Good Night,


cheers,

Ravi.
 
Hi, ravikiran!


Give a look at this file with a only-formula solution:

https://dl.dropboxusercontent.com/u/60558749/Conversion%20of%20Date%20slots%20into%20Dashboard%20%28Bookings%20of%20event%20house%29%20-%20Allocation%20Template-2%20%28for%20ravikiran%20at%20chandoo.org%29.xlsx


In worksheet Hoja1 I slightly modified your input data maintaining the dashboard structure.

Range A:E in light blue is your input data as today, range F:M in light orange is a helper range for distributing booking hours along days, range O2:V13 is your dashboard.


Formulas for helper range:

F : dummy, just used for SUM

G:M : =SI(Y(G$1>=$C2;G$1-$C2+1<=ENTERO(($E2+8-1)/8));MAX(0;MIN(8;$E2-SUMA($F2:F2)));"") -----> in english: =IF(AND(G$1>=$C2,G$1-$C2+1<=INT(($E2+8-1)/8)),MAX(0,MIN(8,$E2-SUM($F2:F2))),"")


Formulas for dashboard:

O4:O9 : Houses

P4:V9 : Booked hours, =SUMAR.SI($A$2:$A15;$O4;G$2:G15) -----> in english: =SUMIF($A$2:$A15,$O4,G$2:G15)


Conditional format for dashboard booked hours:

1st, red interior, red font: =Y($O4<>"";P4=8) -----> in english: =AND($O4<>"",P4=8)

2nd, orange interior, standard font: =Y($O4<>"";P4>0) -----> in english: =AND($O4<>"",P4>0)

3rd, green interior, green font: =Y($O4<>"";P4=0) -----> in english: =AND($O4<>"",P4=0)

Same color for interior and font where booked hours shouldn't be displayed.


Just advise if any issue.


Regards!
 
You must log in or register to reply here.
Back
Top