How to create a rule depending on date periods?

[visakov]

Hello,
I have a maybe not so significant problem, but for me it exists!
So! I have a table whitch helps me to manage my guests and their rent. For example: i have a person who will arrive ate the 15.06.2012 and departure at the 26.06.2012. But the problem is that for this period i have two price ranges , first is until the 19.06.2012 and the second starts from the 20.06.2012 and i need to calculate the price for his stay. Please, be advised that sometimes the period for stay takes 3 or 4 price ranges!
Please, help me to resolve that propblem!

Thank you in advance for your time and attention!

[darknation144]

Can you post a sample work book so we can see how you have structured it.

[MarvinP]

Hi visakov and welcome to the forum,

Find the attached with one way to do your problem. I have10 rooms to rent. The charge on a room varies based on the day of week. I made a random charge table to the right of the pivot table. Then used a VLookup formula to find the cost of a room on that day of week. A Pivot Table at the end shows the amount to charge the customer for their stay.

There are many ways to do this problem. This is just one of them. I hope it gives you some ideas on how to solve your problem.

[visakov]

Hi visakov and welcome to the forum,

Find the attached with one way to do your problem. I have10 rooms to rent. The charge on a room varies based on the day of week. I made a random charge table to the right of the pivot table. Then used a VLookup formula to find the cost of a room on that day of week. A Pivot Table at the end shows the amount to charge the customer for their stay.

There are many ways to do this problem. This is just one of them. I hope it gives you some ideas on how to solve your problem.

Thank you Marvin,
Your model is very helpful, but i have a problem with dates. Do you have an idea how to combine dates and prices:

Example: 01.06.2012 - 30.06.2012 price is 85,00 per night.
01.07.2012 - 31.07.2012 price is 100,00 per night.
01.08.2012 - 31.08.2012 price is 125,00 per night

The period is 26.6.2012 - 15.08.2012, that means 5 days for the period 1
31 days for the period 2, and 14 nights for the period 3

How to calculate the price using excel?

Your model is great for the the price based on the weekdays but not on date. Can you help me with this?

[visakov]

Can you post a sample work book so we can see how you have structured it.

Hello! Thank you in advance for your intend to help me!

Here is the sample of my workbook!
rental-guest-stay-calculation-table.xlsx

Thank you once again!

Kind regards!

[MarvinP]

Hi,
You should never have multiple things in a single cell. Having a start and end data with a dash inbetween is a problem. Separate your dates out to 2 cells at the least. I'd prefer you have a single date column.

As for the lookup table. I have it as Rooms are rows and day of week in the columns. You could build a table with a lot of dates as rows and Rooms as the columns. I think this would solve your problem. Create a different lookup table instead of the one I've attempted.

I hope this helps.

[visakov]

Hi,
You should never have multiple things in a single cell. Having a start and end data with a dash inbetween is a problem. Separate your dates out to 2 cells at the least. I'd prefer you have a single date column.

As for the lookup table. I have it as Rooms are rows and day of week in the columns. You could build a table with a lot of dates as rows and Rooms as the columns. I think this would solve your problem. Create a different lookup table instead of the one I've attempted.

I hope this helps.

Hello Marvin,
Thank you for your quick answer.
Actualy i don`t have a multiple things in one cell. It is separated. As an attachment i am sending to you the real table i want to use. Could you please chck it and give me an advice. I have tought about this to make a table with multiple columns (a single column for every date) and use VLOOKUP, but i have a problem with this how to "tell" to Excel that using the starting date it has to count all the dates from this price range and after this to start counting the dates from the nex price range.

rental-guest-stay-calculation-table.xlsx

Thank you in advance once again!

Kind regards,
visakov

[visakov]

Hi,
You should never have multiple things in a single cell. Having a start and end data with a dash inbetween is a problem. Separate your dates out to 2 cells at the least. I'd prefer you have a single date column.

As for the lookup table. I have it as Rooms are rows and day of week in the columns. You could build a table with a lot of dates as rows and Rooms as the columns. I think this would solve your problem. Create a different lookup table instead of the one I've attempted.

I hope this helps.

I hope with this additional explenation i will cristalize the problem. Let`s say it like that. I have two dates - arriving and departure. This time range (between two dates) takes two price ranges. Every date is in a single column. How to "tell" to excel to count the days of the first price range and then of teh second price range!

I hope it will help!

[MarvinP]

Hi visakov,

You need to build a table with the end dates of the price change. Then you need to do a VLookup using TRUE as the last argument. I've done this on your second sheet. I think this will solve your problem but it will take an extra step of listing all the dates a guest stays. See the attached.

[lk26]

Hi visakov,

You need to build a table with the end dates of the price change. Then you need to do a VLookup using TRUE as the last argument. I've done this on your second sheet. I think this will solve your problem but it will take an extra step of listing all the dates a guest stays. See the attached.

I'm hoping you maybe able to help me also. I have a slightly similar situation. I have a pivot table with many separate accounts. each account has its own set of patients who all have equpiment. each piece of eqipment have a different daily charge. i need a formula or macro where i can calculate how much that patient will be charged for the month. for example, Account 1 patient A recieved a wheelchair on 3/3/12 at $1.25 per day and is currently still using, so i would like to know how much Patient A will be charged for the rest of the month. at the same token, Account2 Patient A recieved a wheelchair at a rate of $0.50 per day on 3/12/12 and returned it on 3/23/12.

this is all on the same pivot table and i am having trouble ust getting it to calculate for the month. understand that I don't know much about macros just yet. I am trying to teach myself and I have come to a point where I just can't seem to figure it out

Please Help

Sincerely

[MarvinP]

Hi lk26,

You need to start your own thread and attach a sample workbook and we can help. I like pivot tables.