Excel room inventory

[Shahuru]

When I put a date range in cell A1 (1/12/18) and cell A2 (5/12/18) I want to add the dates 1 2 3 4 5 to separate cells like, cell A3(1) cell A4(1) cell A5(1).... the idea is when i enter start date and end date, to add in between dates to a separate cells
I need to make a room inventory chart. Lets say total 6rooms avilable for the mentioned date. As when i put start date and end date as above, i want it to be deduced from the inventory and show the 5 rooms available as 1 is booked already. Hope you get what im trying to explain. Need urgent solution. Regards
SR

 

[My Aswer Is This]

Try this:
Assumes you have StartDate in Range("A1")
And StopDate in Range("A2")

Sub Add_Dates()
'Modified  12/4/2018  9:40:21 PM  EST
Application.ScreenUpdating = False
Dim StartDate As Date
Dim EndDate As Date
Dim i As Long
Dim ct As Long
StartDate = DateValue(Range("A1").Value)
EndDate = DateValue(Range("A2").Value)
ct = DateDiff("d", StartDate, EndDate)
For i = 2 To ct
    Cells(i + 1, 1).Value = DateAdd("d", i - 1, StartDate)
Next
Application.ScreenUpdating = True
End Sub

 

[Shahuru]

When I put Start date and End Date, I want the in between dates to fill the dates with "1" in the table and sum up with the existing numbers too. The idea is to manage a room inventory by adding start date and end date for each booking.


[TABLE="width: 485"]
<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2792;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2164;"> <col width="35" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1233;" span="14"> <tbody>[TR]
[TD="width: 80, bgcolor: #F2F2F2"]01-Dec[/TD]
[TD="width: 62, bgcolor: #DDEBF7"]Room Type[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]1-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]2-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]3-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]4-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]5-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]6-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]7-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]8-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]9-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]10-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]11-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]12-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]13-Dec[/TD]
[TD="width: 35, bgcolor: #FCE4D6"]14-Dec[/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F2F2"]04-Dec[/TD]
[TD="bgcolor: transparent"]Deluxe[/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]King[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #DDEBF7"]Room Type[/TD]
[TD="bgcolor: #FCE4D6"]15-Dec[/TD]
[TD="bgcolor: #FCE4D6"]16-Dec[/TD]
[TD="bgcolor: #FCE4D6"]17-Dec[/TD]
[TD="bgcolor: #FCE4D6"]18-Dec[/TD]
[TD="bgcolor: #FCE4D6"]19-Dec[/TD]
[TD="bgcolor: #FCE4D6"]20-Dec[/TD]
[TD="bgcolor: #FCE4D6"]21-Dec[/TD]
[TD="bgcolor: #FCE4D6"]22-Dec[/TD]
[TD="bgcolor: #FCE4D6"]23-Dec[/TD]
[TD="bgcolor: #FCE4D6"]24-Dec[/TD]
[TD="bgcolor: #FCE4D6"]25-Dec[/TD]
[TD="bgcolor: #FCE4D6"]26-Dec[/TD]
[TD="bgcolor: #FCE4D6"]27-Dec[/TD]
[TD="bgcolor: #FCE4D6"]28-Dec[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Deluxe[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]King[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Shahuru]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]S Date
[/TD]
[TD]E Date
[/TD]
[TD][/TD]
[TD]1 Dec
[/TD]
[TD]2 Dec
[/TD]
[TD]3 Dec
[/TD]
[TD]4 Dec
[/TD]
[TD]5 Dec
[/TD]
[/TR]
[TR]
[TD]King
[/TD]
[TD]1 dec
[/TD]
[TD]4 dec
[/TD]
[TD]King
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deluxe
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

It works with date data , hence I want it to fill with numeric number "1 " as in the above table. S date is 1Dec E Date is 4 Dec. and Type King , and fil the dates as in red. If I select Deluxe as a Type and S date 3Dec and E date 5Dec , it should add 3dec 1 into 5 , 4dec 1 into 6

Appreciate your help

Many Thanks,
SR

 

[majidsiddique]

hi Shahuru,
if you record this transaction in this way,
try it,
A1 to A10, B1 to B10.
[TABLE="width: 150"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]01/07/2018[/TD]
[TD]king[/TD]
[/TR]
[TR]
[TD]02/07/2018[/TD]
[TD]king[/TD]
[/TR]
[TR]
[TD]03/07/2018[/TD]
[TD]king[/TD]
[/TR]
[TR]
[TD]04/07/2018[/TD]
[TD]king[/TD]
[/TR]
[TR]
[TD]05/07/2018[/TD]
[TD]king[/TD]
[/TR]
[TR]
[TD]06/07/2018[/TD]
[TD]deluxe[/TD]
[/TR]
[TR]
[TD]07/07/2018[/TD]
[TD]deluxe[/TD]
[/TR]
[TR]
[TD]08/07/2018[/TD]
[TD]deluxe[/TD]
[/TR]
[TR]
[TD]09/07/2018[/TD]
[TD]deluxe
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 750"]
<colgroup><col span="10"></colgroup><tbody>[TR]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]02/07/2018[/TD]
[TD="align: right"]03/07/2018[/TD]
[TD="align: right"]04/07/2018[/TD]
[TD="align: right"]05/07/2018[/TD]
[TD="align: right"]06/07/2018[/TD]
[TD="align: right"]07/07/2018[/TD]
[TD="align: right"]08/07/2018[/TD]
[TD="align: right"]09/07/2018[/TD]
[TD="align: right"]10/07/2018[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

=COUNTIFS($B$1:$B$30,$E$2,$A$1:$A$30,">="&P1,$A$1:$A$30,"<="&P1)
criteria mention on E2.

Thanks

 

[majidsiddique]

hi,
=COUNTIFS($B$1:$B$30,$E$2,$A$1:$A$30,">="&G1,$A$1:$A$30,"<="&G1)
copy drag in colume
Thanks