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.

01-Dec

Room Type

1-Dec

2-Dec

3-Dec

4-Dec

5-Dec

6-Dec

7-Dec

8-Dec

9-Dec

10-Dec

11-Dec

12-Dec

13-Dec

14-Dec

04-Dec

Deluxe

1

1

1

4

4

4

King

2

3

6

11

Room Type

15-Dec

16-Dec

17-Dec

18-Dec

19-Dec

20-Dec

21-Dec

22-Dec

23-Dec

24-Dec

25-Dec

26-Dec

27-Dec

28-Dec

Deluxe

2

2

2

King

<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> </tbody>

 

[Shahuru]

Type	S Date	E Date		1 Dec	2 Dec	3 Dec	4 Dec	5 Dec
King	1 dec	4 dec	King	1	1	1
			Deluxe			5	6	2

<tbody>
</tbody>

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.

01/07/2018	king
02/07/2018	king
03/07/2018	king
04/07/2018	king
05/07/2018	king
06/07/2018	deluxe
07/07/2018	deluxe
08/07/2018	deluxe
09/07/2018	deluxe

<colgroup><col span="2"></colgroup><tbody>
</tbody>

01/07/2018	02/07/2018	03/07/2018	04/07/2018	05/07/2018	06/07/2018	07/07/2018	08/07/2018	09/07/2018	10/07/2018
1	1	1	1	1	0	0	0	0	1

<colgroup><col span="10"></colgroup><tbody>
</tbody>

=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