Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I want to prevent same START TIME for the same DATE. EX.
User1:
Column A = Date of Meeting - 29-OCT-09
Column B = Start Time - 02:00 PM
Column C = End Time 03:00 PM
Column D = StatusNow if any user say user selects date as 29-OCT-09 and Start Time as 02:00 PM it should prompt a mssg as duplicate entry etc. At the same time a user can choose any time for 29-OCT-09. Also, we need to keep in mind that for any other date the same time 2 PM in this case can be choosen
You should try to use a subject line more relevant to the contents of your post. If everyone posted with a generic subject line like "Excel Help" it would be impossible to tell one thread from another. It also makes it harder to scan the archives for threads of interest.
You didn't tell us how users are "selecting" dates and times, but Data Validation sounds like the way to go.
With a list of dates in A1:A10 and a list of times in B1:B10, this will prevent a duplicate date and time combination being placed in E1 and F1.
Highlight E1 and F1 and use:
Data Validation...Allow...Custom...Formula
=SUMPRODUCT(($A$1:$A$10=$E$1)*($B$1:$B$10=$F$1))=0
-1 
Just a quick comment:
Using DerbyDad03’s formula, it would be prudent to also use some Drop Down Lists for the time cells.
An enterprising user simply has to use a time like 2:01 PM and it will pass.
The Drop downs will force them to use just the times you specify.
0
Hi,
You could include a room booked display, together with availability checking.
Booking input is: date in cell A2, start time in B2, end time in C2 and name in D2
Availabilty will be shown in E2.
Date Start Time End Time Name Availability 14-Oct-09 11:00 13:00 Dept.B BookedStarting at H3 are the 1 hour slots, with dates in row 2 and times in column G
Date Time 13-Oct-09 14-Oct-09 15-Oct-09 16-Oct-09 6:00 7:00 8:00 9:00 10:00 11:00 12:00 Finance 13:00 Finance Dept. A 14:00 Mgt mtg Finance 15:00 16:00 17:00The formula in E2 is:
=IF(OR(IF(VLOOKUP(B2,$G$3:$T$14,MATCH(A2,$H$2:$T$2,0)+1)="",FALSE,TRUE),IF(HOUR(C2-B2)>1,IF(VLOOKUP(C2-(1/24),$G$3:$T$14,MATCH(A2,$H$2:$T$2,0)+1)="",TRUE,TRUE),FALSE)),"Booked","Available")The range H2:T2 is the range of dates on the table and G3:T14 is the whole table below the dates starting with the time column.
As Mike says, add data validation to the entry.
For A2 use dates between =Today() and say =Today()+14 if you are going to keep 2 weeks of bookings on display
For B2 use times between your earliest booking available and your latest booking time
For C3 use times one hour later than in B2Now add a command button, label it "Commit" and add this code to it
Option Explicit Private Sub CommandButton1_Click() Dim dt As Date Dim st As Date Dim et As Date Dim nm As String Dim av As String Dim rngCell As Range Dim rngDatastart As Range Dim rngTablestart As Range Dim intHoriz As Integer Dim intVert As Integer Dim intSlots As Integer Dim n As Integer On Error GoTo ErrHnd With ActiveSheet 'set data origin Set rngDatastart = .Range("A2") 'set table origin Set rngTablestart = .Range("H3") 'get data entered dt = rngDatastart.Offset(0, 0).Value st = rngDatastart.Offset(0, 1).Value et = rngDatastart.Offset(0, 2).Value nm = rngDatastart.Offset(0, 3).Value av = rngDatastart.Offset(0, 4).Value 'set number of one hour slots used intSlots = Hour(et - st) ' test if we can commit this meeting to the table If av = "Available" And nm <> "" Then 'get column number (date) n = 1 For Each rngCell In .Range("H2:T2").Cells If rngCell.Value = dt Then intHoriz = n Exit For Else n = n + 1 End If Next 'get row number (time) n = 1 For Each rngCell In .Range("G3:G14").Cells If Round(rngCell.Value, 4) = Round(CDbl(st), 4) Then intVert = n Exit For Else n = n + 1 End If Next 'loop for number of slots For n = 1 To intSlots rngTablestart.Offset(intVert + n - 2, intHoriz - 1).Value = nm Next n Else MsgBox "Sorry can't commit as slot(s) not free or booking name not entered" End If End With Exit Sub ErrHnd: Err.Clear End SubIf the time slot is available it will place the name in the slot (or slots, if more than one hour booked).
It needs a check included to ensure that bookings are not attempted past the available times or dates, although data entry validation should have protected against this.If the time slot was not available or if the user had not entered a name, they get a warning message.
Conditional formatting can be added to E2 to alert the user to Available or Booked
Regards
1
Thanks for the solution but I dont want it to be so robust... Let me explain what i am trying. for example as user say user1 books a meeting as for 29-oct with start time as 2 pm and end time as 3 pm..he will put the status as "Booked". Now if user 2 wants the same time slot for the same date he should not be able to do so. However if the status is changed to available user 2 can go ahead and booked the same time slot for the same date.
0
re: Thanks for the solution but I dont want it to be so robust
You've been offered 2 very different soultions so it's really hard to tell which one you are referring to as "so robust".
We also don't have any idea how your worksheets are laid out, so we can't be very specific.
Based on the very generic description you've given us in Response #4 we don't even know if it's one shared worksheet for all users or separate worksheets for each or what.
Is it a worksheet with a bunch of times and dates already in a table or are users entering their own data or what?
How are the users entering the data (if they are) - manual entry, drop-downs, forms, what?
You really need to be more specific.
-1
Hi,
Use three columns
A is date
B is time
C will show statusAs time slots are only one hour, there is no need to enter the finish time.
The status column completes itself.A B C 1 Date Time Status 2 14-Oct-09 8:00 Available 3 15-Oct-09 10:00 Available 4 16-Oct-09 11:00 Available 5 15-Oct-09 9:00 Available 6 17-Oct-09 14:00 Available 7 17-Oct-09 16:00 Available 8 15-Oct-09 10:00 Already Booked
Use data validation as discussed before to control dates in column A and data validation with a drop-down list of times in column BIn column C use this formula (starts in C2)
=IF(A2="","",IF(OR(A2="",B2=""),"Incomplete",IF(SUMPRODUCT(($A$1:A1=A2)* ($B$1:B1=B2))<>0,"Already Booked","Available")))
Note that the formula has been split onto two lines for ease of viewing.
Note the $ signsDrag the formula in C2 down as far as necessary, and where the formula is opposite empty cells in columns A and B, the status will be blank.
When a date and time are entered, if that date and time have not appeared anywhere above it in columns A and B then it will show "Available"
If that date and time have appeared anywhere above it in columns A and B then it will show "Already booked"If someone changes their mind about a booking they have to delete the entry and if there was another entry for the same time and date, its status will change from Already booked to Available.
If a date or a time are entered but not both, the status shows "Incomplete"
Regards
1
Re: re: Thanks for the solution but I dont want it to be so robust.
DerbyDad03.. The users are entering the data manually in a excel sheet. The data that they enter is protected once they save the sheet using a VB code, which means that the data can only be entered in the blank cells of the sheet.
Now once again coming to my problem statement:
Lets say user1 books a meeting as for 29-oct with start time as 2 pm and end time as 3 pm, he will put the status as "Booked". Now if user 2 wants the same time slot for the same date he should not be able to do so.
However if say for instance user1 decides to cancel the meeting he will change the status of "Booked" to "Meeting Cancelled" and any user say user3 or user 2 can enter that particular time slot that was earlier with user1.
Further, we need to keep in mind that a user can choose number of time slots for a particular date. So, the logical expression sould check that also... I am stuck at this point because if I use IF with embedded AND formula...both the conditions should be true...
0
Hi,
The option I proposed provides what you want:
However if say for instance user1 decides to cancel the meeting he will change the status of "Booked" to "Meeting Cancelled" and any user say user3 or user 2 can enter that particular time slot that was earlier with user1.
Except that they don't enter the status - the status is automatic.
To cancel the meeting they just delete the date and time entry.As you didn't want an option that allowed for meetings extending more than one hour, the option proposed considers only the start times, and as such there is no reason for users to enter a finish time. A column with an automatically entered finish time could be included without changing any of the logic.
You said the following:
Further, we need to keep in mind that a user can choose number of time slots for a particular date. So, the logical expression should check that also...I don't understand how this affects the solution.
All the options proposed test to see if the new date and time entered has already been taken.It doesn't matter how many time slots one person books - all that matters is whether the time slot that is being requested, has already been taken, or am I missing something here.
Regards
1
You've told us numerous times what you want to do, but you still haven't told us enough about how your spreadsheet is set up.
You said: The users are entering the data manually in a excel sheet. The data that they enter is protected once they save the sheet using a VB code
You then said: if...user1 decides to cancel the meeting he will change the Status of "Booked" to "Meeting Cancelled"
Obviously, the manually entered data and the Status can't be in the same cell because you've told us that the user can't change the data once it's been entered.
Therefore the cell that contains the Status has to be related to the cells that contain the times or there would be no way for any code or formula to "free up" that time slot and allow the user to change it. Until you tell us how the Status cell relates to the time slots cells I don't think we can help you.
Whether you realize it or not, you are asking for something that is rather complex, yet you said "Thanks for the solution but I dont want it to be so robust."
Sorry, you can't get what you want with simple =SUM(A1+B1) type solutions.
You've already got VBA locking cells and protecting sheets, so you're already using "robust" means and now you're asking for these cells to be unlocked based on other criteria. "Not so robust" is not an option.
Finally, why are you trying to use Excel to reinvent a calendaring application? That's not what it's designed for, so why not find another option?
Look here for options, some of which are free.
0
Point No. 1 I have not told told everybody numerous times what I want to do. Its not my problem if you are not able to understand it DerbyDad.
Others apart from you have understood and provided an effective solution and I thank them and congratulate them for being effective Mentors.
Secondly, dont tell me that excel is not designed for calendaring applications...It is very much capable of it. And I thank Humar and Mike for taking it to that level.
Finally, since my first day you have preaching me with all the etiquettes of this blog, but have you ever realized that how negative is your approach towards mentoring and coaching.
0
re: I have not told told everybody numerous times what I want to do.
Your OP: Now if any user say user selects date as 29-OCT-09 and Start Time as 02:00 PM it should prompt a mssg as duplicate entry etc.
Your Response #4: Now if user 2 wants the same time slot for the same date he should not be able to do so.
Your Response #7:Lets say user1 books a meeting as for 29-oct with start time as 2 pm and end time as 3 pm, he will put the status as "Booked". Now if user 2 wants the same time slot for the same date he should not be able to do so.
That's 3 times you've told us that you are trying to prevent the same time slot from being booked more than once. We got it.
re: Its not my problem if you are not able to understand it
DerbyDad.I've understood your goal ever since you posted it in your OP. However, what I have continually asked about was the relationships between the various pieces of data in your spreadsheet.
For example, I asked you about the relationship between the Status cells and the time slot cells. As far as I can tell, you haven't answered that question. Allow me to explain why I asked that question...
You want to prevent a time slot from being booked twice, but you want the ability to re-use a time slot if the status is changed to Available. I asked for the relationship between the status cell and the time slots because with that information there are other possible solutions.
For example, if you tell us that the Status cell is one cell to the right of the Start Time cell, we could offer code that unlocks that Start Time cell and clears the time so that time slot is available for the next user. Do you see why I was asking that question?
re: Secondly, don't tell me that excel is not designed for
calendaring applications...It is very much capable of it."Capable" of being a calendar and "designed" to be a calendar are 2 very different things. I'm sure that eventually you may accept one of the many solutions offered and end up forcing Excel to be your calendar, but that doesn't mean Excel was designed to be used as a calendar.
I'm not sure why that question upset you so much. All I was doing was offering an alternative: applications that were actually designed to used as a calendar, instead of trying to reinvent the wheel by writing your own. You might note that no where in that list of popular calendaring applications does Excel appear.
I wish you luck in achieving your goal.
0
re:re: I have not told told everybody numerous times what I want to do.
Hi Derbydad03..I'm sorry for any misunderstanding on my part. This is how my sheet is arranged:
Col A - Employee No.
Col B - Start Time
Col C - End Time
Col D - Status.Now this is what is happenning. user open the excel sheet from a shared location and enter the details. They can make changes till the time they have not saved the sheet. but once they have saved the sheet they cannot make any changes. Please Note: Col D is not in this protection area.
0
Hi,
Is this part of the same room booking problem or is this something else.
I don't recall an employee number in the earlier discussions, also Column A was date not employee number.
As there is now no date, what are you trying to achieve. Are the same times on different days in conflict.
Also are we still dealing with one hour time slots?
Regarding 'Please Note: Col D is not in this protection area', does that mean that users can change the status in column D.
Regards
1
All sorry, i was in a hurry while typing out this...Yes it is to the same thing. Once again the updated columns are:
Col A - Employee No.
Col B- Date
Col C - Start Time
Col D - End Time
Col E - Status.The 1 hr time slot cannot be mandatory...as the meeting can be of 10 mins also. users are allowed to change the status. So initially I will put it as "Booked" but if my meeting cancells I will change the status to "cancelled" thereby making it available for use. But till the time the status is booked no other user can take that time slot.
0
Hi,
This is now a fairly complicated set of requirements.
When a new entry is created, specifically:
Col B- Date
Col C - Start Time
Col D - End Time
The code will have toa. look for all entries with same date
b. If same date, test if Start time is between any existing start and finish times. If so flag up 'not available' (but see 'e' below)
c. If same date, and the start time is between any existing start and finish times, test if end time is later than the end time for that pair of times and then test if that end time is later than any other start times for the relevant date and flag up not available. (but see 'e' below)
d. If finish time is later than any other start times in 'c', the process will have to loop to see if it is later than the relevant finish time and check if it is between any other start and finish times for the relevant dates. (For example a one hour meeting may cross several shorter meeting times, and each will have to be tested for status).
e. Before flagging up 'unavailable' for each conflicting time slot test the Status for one or more entries and if any Status shows that the room is booked, return unavailable, else return 'available'
f. Then, as the user is able to change their booking Status provide a way to limit the user's ability to enter a booked status when the code indicates that the room is not available for that time slot.
g. Re-run the code against all entries when any Status is changed by a user, as one or more entries flagged unavailable, may now be available.
h. Decide what to do if an 'unavailable' slot is now 'available'. Is the user Status changed to Booked automatically, or does the code initiate an e-mail to the employee identified in column A.
Is my understanding of your current requirements correct?
The change to variable length time slots certainly increases complexity and I would not be prepared to commit to such a complex program.
Regards
1
Hi Humar,
You understood right. Lets do this to make things simpler:
a. Check for the Start Times for the same date. If the Same start time that the current user is choosing has already been choosen by another user and the status is "Booked" or "Not Available"..then it must stop.
b. If the Same start time that the current user is choosing has already been choosen by another user and the status is "Cancelled" or " Available"..then it must allow that entry.
c. lets try putting checks in Col B, Col c and Col E. for instance... Col B = 27 Oct 09, Col C = 10:00 AM, Col E = Booked.. then for current user 10:00 AM in Col C becomes restricted. BUT for instance Col B = 27 Oct 09, Col C = 10:00 AM, Col E = Cancelled.. then for current user 10:00 AM in Col C becomes available.
d. Col e is not in the protected area...which means that users can change it even after they have saved their entry. Which is not the case with other columns...other columns are protected once the user saves the sheet.
0
Hi,
Your revised requirements don't meet the needs of the system as previously stated.
Now you say you just want three checks:;
1. If start date (this entry) = any existing start date, then:
2. If start time (this entry) = any existing start time (same dates), then:
3. Check status (Booked / Canceled / Available).The user making this entry gets a visual warning that this start time on this date is Not available.
OK, but as meetings are not fixed duration (you referred to some meetings of 10 minutes) and therefore not fixed start times, comparing start times will result in rooms appearing to be available when there are meetings already booked for part of the time requested.
Your revised criteria are essentially met by the formula in Response #6, with some changes to columns.
However I offer the following, (based on Response #6):
Column A Employee Number
Column B Start Time
Column C Finish time (although it is not used in the calculation)
Column D Users status Either "Requested" or "Canceled" (The user can't enter Booked because they don't know if the slot is available)
Column E "Available" or "Not Available" (generated by the formulas)
Column F "Booked" or blank (If the generated status in column E is Available and the user has not entered Canceled in column D, then it shows Booked)Lock columns F & G so that the user cannot erase the formulas in them.
Here is an example of the output:
A B C D E F G 1 Emp. # Date Start Finish Requested Available or Booked Time Time or Canceled Not Available 2 27 27-Nov-09 10:00 10:59 Cancelled Available 3 41 29-Nov-09 14:00 14:59 Requested Available Booked 4 57 27-Nov-09 10:00 10:59 Cancelled Available 5 65 27-Nov-09 14:00 14:59 Requested Available Booked 6 66 28-Nov-09 10:00 10:59 Cancelled Available 7 94 29-Nov-09 14:00 14:59 Requested Not Available 8 143 29-Nov-09 10:00 10:59 Requested Available Booked 9 70 29-Nov-09 14:00 14:59 Requested Not Available 10 160 28-Nov-09 10:00 10:59 Requested Available Booked 11 193 28-Nov-09 14:00 14:59 Requested Available Booked 12 127 28-Nov-09 10:00 10:59 Requested Not Available 13 6 29-Nov-09 14:00 14:59 Requested Not Available 14 12 27-Nov-09 10:00 10:59 Requested Available Booked 15 141 28-Nov-09 14:00 14:59 Requested Not Available 16 72 27-Nov-09 10:00 10:59 Requested Not Available 17 81 28-Nov-09 14:00 14:59 Requested Not AvailableThe value "Available" in F2 is entered as text as the first entry has nothing to check against.
In F3 enter this formula:=IF(A3="","",IF(OR(B3="",C3="",D3="",E3=""),"Incomplete", IF(SUMPRODUCT(($B$2:B2=B3)*($C$2:C2=C3)* ($E$2:E2="Requested"))<>0,"Not Available","Available")))
Note: I have split the formula over three lines for ease of viewing
Drag the formula down as required.In cell G2 enter the following formula:
=IF(AND(E2="Requested",F2="Available"),"Booked","")
Drag the formula down as required.If a user changes the status of an entry from Requested to Canceled, the next meeting (if any) for that date/time slot changes from Not Available to Available and the message "Booked" appears in column G.
As suggested by others, data validation on entries is required.
The date must be in a format that Excel recognizes as a date, Start time must be both in a format that Excel recognizes as a time and restricted to exact 'on the hour' start times or else minor start time differences such as 10:00 and10:01 will not be recognized as the same.
The User status in column E must be restricted to Requested or Canceled, as these words are used by the formulas and must be exact matches.Regards
1  |
 |
 |
| Login or Register to Reply | |
| Login | Register |
| Ads by Google |
