I have a question about building an excel formula involving dates and costs. I am dealing with a rooming list for hotel guests and room costs based on dates. I'm working with a single spreadsheet which MUST have filterable columns for:
guest name (about 250 people)
hotel name (there are four different hotels)
date in and date out (they must be separate columns as I often need to sort by ascending or descending)
total cost per room
I'd gladly add in any other columns to allow me to build the formula necessary but the general layout must be like the grid linked here: https://dl.dropboxusercontent.com/u/5864034/Test_Excel.xls
What's causing me a problem is: each hotel has different rates and these rates vary according to the date! The one variable that remains the same is that all four hotels increase/decrease their prices on the same dates. Here's an example of one hotel's costs: the room price from Jan 1- Jan 25 = $129. From Jan 26-Jan 30= $159. From Jan 31- Feb 2 = $282. From Feb 3-Feb 8= $159. Is there any way to create a formula that considers the date as a factor in calculating the total cost per room?
I was thinking of adding four additional columns, one for each price range (one: Jan 1- Jan 25; two: Jan 26- Jan 30; three Jan 31- Feb 2; four: Feb 3- Feb 8) so that I can use the IN and OUT date for each guest and tally up how many nights per price range and then multiply it by the rate and then add up those sums in a final column with the grand total for the room for that person. I imagine the formula would need to refer to the IN and OUT date to count how many days are in each price tier to then multiply by the room cost, but I can't even figure out how to do this! Any help would be greatly appreciated! I've uploaded a sample of the spreadsheet layout I need to use in dropbox: https://dl.dropboxusercontent.com/u/5864034/Test_Excel.xls