Assume per diem travel rates vary by block of dates (from and to). So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day. Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day. With different travel dates (from and to) specified per traveller, the task is to determine total travel expenses per individual.
You may refer to my solution in this workbook.
I had a similar question about an excel formula involving dates and costs.
I am dealing with rooming lists for hotel guests and room costs based on dates. I’m working with a single spreadsheet which contain columns for:
guest name (about 250 people)
hotel name (there are four different hotels)
date in
date out
number of nights
and TOTAL cost for the room for the entire stay.
Problem is, each hotel has different rates and the rates vary according to the date! The one variable that remains the same is 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?
I need for the data to be filterable though, especially by hotel name, and IN/OUT date.
I might have to resort to 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 tally up how many nights per price range with a subtotal for each tier to then get another column with the grand total. The formula would need to refer to the IN/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 spreadsheet to dropbox: https://dl.dropboxusercontent.com/u/5864034/test.xls
thank you
You are welcome.
Hello again, sorry… I’m incredibly impressed by your expertise but I realize that I can’t seem to make that formula work on the spreadsheet I had already started. I keep getting stuck. I’ve linked the actual spreadsheet I am working on in hopes that you can help me sort it out because I am officially lost.
infinite thanks!
https://dl.dropboxusercontent.com/u/5864034/Incomplete.xls
Thanks again!
You are welcome.
Hi Ashish. Thanks for posting this great post. Also, I appreciate that you include a workbook for your solution. I haven’t checked it, but soon I’ll do it. But can you help me with this problem? If an employee travels to a city where per diem is $125 per day, how much will that employee receive for each day they stay there on business? Please keep sharing informative content.
You are welcome. Please share some data, explain the question and show the expected result.
Determine cumulative expenses per employee when per diem rates vary by block of dates
{ 10 Comments }