Excel Automation Challenge #1

Excel Automation Challenge #1

Today I was trying to develop automation for one of my Rental business client from USA.

The main crux of the solution was to arrive at the table of dates for each month bifurcated in to Actual Stay Dates and Holidays taken (there is only one incidence of such instance of Holiday during the entire stay when the guest is not charged)

I am sharing seven scenarios and the solution I created with the help of formula to achieve the automation.

The screenshots are self explanatory to understand what I am trying to achieve.There are four input point namely “Check-in and Check-out Dates” and “Break Start and End Dates”. Based on these four dates, I need to create the table in three parts for each month:
1. Stay Dates Start and End (Part 1)
2. Break Dates Start and End
3. Stay Dates Start and End (Part 2)

Generally when I come across such situation almost on daily basis, I store it as caselaw in my dropbox for later reference in blogs, courses or presentations but today I thought to engage with you to make you think and develop the thinking process to arrive at the solution.

Feel free to comment if you need any clarification and I will wait for some days and then provide the link to solve file in comment section below. You can also share if you arrive at the solution. Try entering all seven scenarios to test your solution.

Please share with your friend who you think might find it interesting!

Happy Brainstorming!!

SS 7 SS 6 SS 4 SS 5 SS 3 SS 2

 SS 2 SS 3 SS 5

Comments

Subscribe for Newsletter