DateDiff Handling in Excel VBA

[Sojourner26]

May I know if there's way to code DatePicker1, DatePicker2, and DateDiff values for an inn booking system to allow daily occupancy and revenue trend monitoring purposes. The coding will allow color-coding, breaking down a DateDiff value to "Count" and daily room rate entries in a table. I tried Pivot table but I can only select one date as X-axis variable. Please see attached Excel file showing the expected tables to be generated in the project.

My sincerest thanks for any help on this query.

Sojourner26

 

[NARAYANK991]

Hi ,

First , please segregate the areas for data entry , calculation purposes and report purposes on different tabs. Having everything together is unnecessary , when Excel allows hundreds of tabs.

Secondly , your data entry scheme which has the room number , the room tariff and the start and end dates is not conducive to simple formulae because the room number is not repeated.

If the room tariff is not likely to change in the short term , then it can be stored along with the room number in a separate table , where for 3 rooms there will be only 3 entries.

The main data entry section will only consist of the room number , the start date and the end date ; this data entry will not be sorted room number-wise , but will be in chronological order , so that every time a fresh booking is received , it is entered into this table.

Can you revise your scheme according to these points ?

Narayan

 

[Sojourner26]

Hi ,

First , please segregate the areas for data entry , calculation purposes and report purposes on different tabs. Having everything together is unnecessary , when Excel allows hundreds of tabs.

Secondly , your data entry scheme which has the room number , the room tariff and the start and end dates is not conducive to simple formulae because the room number is not repeated.

If the room tariff is not likely to change in the short term , then it can be stored along with the room number in a separate table , where for 3 rooms there will be only 3 entries.

The main data entry section will only consist of the room number , the start date and the end date ; this data entry will not be sorted room number-wise , but will be in chronological order , so that every time a fresh booking is received , it is entered into this table.

Can you revise your scheme according to these points ?

Narayan

 

[NARAYANK991]

Hi ,

Please comment.

And if you agree with the points made , please revise your workbook structure , and re-upload the revised version.

Narayan

 

[Sojourner26]

Dear Narayan,

Thank you for the reply and for the guidance.

I've attached the booking system I've created. I already trialed using it. The system now posts all data from the booking UserForm to the Database sheet. I would like now to do data analysis under the Occupancy and Revenue sheets. I would like to show tables in these sheets looking like what I described in my first post.

Hoping the attached file addressed your request for further information.

I'm grateful for all your help.


Sojourner26

 

[NARAYANK991]

Hi ,

Thanks for uploading your working file.

What is the format for the report on the Occupancy and Revenue tabs ?

Can we transfer any format from the workbook you uploaded initially ?

Narayan

 

[Sojourner26]

Hi,

I've attached a picture showing how the revenue page will look like. The picture is my target table and graph in the Revenue sheet which would appear after selecting a start date and end date. In the occupancy sheet, the same table and graph will appear but will show the booked days data.

I've also attached the source data for the graph and table.

I'm okay with the transfer of format.

Many thanks.

 

[NARAYANK991]

Hi ,

I will take some time to look into this. I hope you can wait.

In case anyone else would like to help out , that would be nice.

Narayan

 

[NARAYANK991]

Hi ,

Under the Status column , there are 4 categories :

Paid , Confirmed , Unconfirmed , Cancelled.

When you want the revenue figures displayed , is the Paid category amount alone to be considered ?

Why should the Cancelled category be considered in the Cost column , since once a room booking is cancelled , it is a matter that no money has come in or gone out ?

Narayan

 

[Sojourner26]

Hi,

I'm building the system based on a YouTube tutorial posted in 2015. Unfortunately, the author just stopped at the generation of the booking calendar. The sample data I sent is from the template he allowed to be downloaded from his website. The status codes are from his system.

In the booking system I'm creating, the status would be just "Booked" once the booking system is filled. It should have a payment acceptance section which I will create later. This "Unconfirmed" status would make no sense if payment is received during a booking event through a charge against a credit card. I guess it's there as if you're a new player in a business, establishing a link to a credit card company would not happen in an instant. The "Confirmed" status would occur once the guest appears in the hotel and make a deposit or full payment during Check-in.

In the calendar of the proposed booking system, the status changes to "Checked In" when the guest physically arrives in the hotel. When the guest checks out, another color change would occur. The calendar is the perpetual type so whatever entry gets reflected in it becomes a permanent entry.

I cannot get the color-coded calendar running despite the VB code being provided in the website. I did consider contacting the author but in his "Contact Us" page, he could not promise a quick reply due to the volume of queries or emails he receives.

I found 2 other YouTube videos referencing the tutorial I mentioned. The first one shows the calendar and the exact data table but no attribution to the original author was made. The second video features the calendar adopting the exact color-coding scheme. Should I provide links to the videos and the tutorial website? Hoping I did not violate any posting guidelines.

Another feature of my booking system would be to allow editing of a booking if a guest decides stay to shorten or lengthen his stay. A record per guest will be created to reflect events from the booking up to the check out and any changes in between.

As for the "Delete Booking" feature, booking deletion is expected to be rare. I think this event will be considered Unrealized Income but would create a hassle on the part of the hotelier in a real situation due to the need to process refund. However, in the calendar, this booking deletion will stay permanent through a color change.

There is no need at this point to reflect whether the revenue to be summarized is unrealized or realized income. Basically the revenue to be graphed is simply the product of booked days and room rate.

This is not urgent so please take your time.

Many thanks.