U.S. government per diem rate table - season dates month and day only

[DR_iDE]

Per diem is a daily cash allowance paid to people traveling on business, it varies by location of travel.

There are per diem rates for foreign locations published monthly by the State Department. They adjust for changes in prices and exchange rates. You can download them in an Excel file. Attempting to create a lookup function using the Excel file data however is very difficult because some of the rates begin and end based upon "season" dates (for a higher rate during summer for instance) and those dates are only listed as MM/DD. In cases where the season extends from a month late in the year to one early in the next, it is in two different years. There are also effective dates, for new rates, and expiration dates, for when a rate is no longer in effect. Trying to search the table using SUMIFS for location and date seems to be impossible, mainly due to the season date issue.

The Defense Department maintains the rates for some locations as well, and they compile the GSA rates for continental U.S., the State Department foreign rates, plus their rates. And they have a "relational" file that is in XML format. But even that has the truncated season dates of just the month and day. The relational file does however appear to retain expired rates for a period of time unlike the State Department file which drops them off when a new rate is effective.

The goal would be to create an Excel file with a lookup formula, perhaps with drop-downs to select the location, that looks up the correct per diem rate for any given date. It can't go back forever, but at least a few months would be needed to give people time to return from a trip and then fill out their per diem reimbursement form. The rate would then be filled in from this lookup rather than them search the website on their own.

Then each month the new rate file would be merged into the existing file so that the latest rates would be returned. This would replace the prior download file not add more records to the existing data. (If the only way to get this to work is to combine the monthly downloads somehow into a multi-month download table, that would be less desirable, but if that is required it would be interesting to know that.)

This should be solved for the Foreign XML download file first. The foreign download contains around 3,000 rows in Excel, while the CONUS file for the United States is nearly 80,000 lines in Excel which would make the entire file a bit large to work with. And if a formula is developed on the foreign data it would likely work on the CONUS file as well.

Having worked with this and being unable to come up with a formula I wonder what use the download files are in the Excel format, if other users just manually search the data in Excel rather than use an automated lookup, which sort of defeats the purpose of having the data in Excel.

The most complex location is Sapporo Japan with four different seasons one of which extends from 12/01 - 03/14. That is a good part of the data to work with to try to develop a way to look up the per diem rate for any selected date.

The Defense Department XML files can be found at this link:
Per Diem Rate Files

If you look at this I am sure you will find it to be an interesting challenge.

If you determine that the data they provide is in fact not complete enough to do such a search, that might be helpful in getting them to research this and change the format of their downloadable file.

But it may be that if certain "assumptions" are made, based upon the date of the download file, the year for the season begin and end dates could be backed into, and then along with the effective dates, a search made.

Thank you and good luck,
David

 

[Dr. Demento]

Dave,

Because of your questions, I only just now saw the Excel files for the OCONUS data (I cannot find any .xml file for DoS OCONUS). I used the webpage Print output for the OCONUS data.

The lack of Expiration Date applies to DoS OCONUS data only; the DoD updates their info much more frequently than DoS (there are some DoS effective dates going back to the 1990's!). Per the DoS, a rate is effective until a new effective date is posted whereas the DoD specifies the expiration date of each rate. Because the sub errors out if there's nothing to compare the trip date, I had to put in some value and I arbitrarily used one year from the Start Date -- you can change it if you like.

Your explanation of dates (Start date 10/15 to End date 3/15 --> 10/15/16 to 3/15/17) is correct.

I evaluated the Effective/Expiration dates BEFORE the Start/End dates; I'm not sure if that entirely alleviates your concerns about inadvertant errors in getting the incorrect rates but my hope was that in ordering it by first evaluating the Effective/Expiration date and THEN the Start/End date, I would get the correct rate; this expectation was driven by the fact that there is a full date for the Effective/Expiration dates but only a partial date for the Start/End dates. Going from known (Eff/Exp dates) to less known (Start/End dates) is always good practice. Limited testing functioned as expected, but like I mentioned earlier, there may be quirks in the data that I didn't account for. Only time/testing will tell.

As far as using the download date of the file, if you would like to incorporate that, it's up to you but I don't believe there would be value added. However, like I pointed out earlier, because DoS rates do not have an expiration, I don't believe use of the file download date would provide any added benefit, but I may be overlooking something.

In regards to back-solving - the reason I incorporated the output in col K was to provide at least a gross-level error check (if more than one row remains after filtering). For more detailed error-check, the anomalies in the dataset are going to address the coding requirements. It just occurred to me that if you added (as output) the Effective/Expiration and Start End Dates, you could use conditional formatting to determine if the Trip Date fell within the date parameters. But that's a topic for a different thread!

Good luck!

 

[Rhodie72]

Per diem is a daily cash allowance paid to people traveling on business, it varies by location of travel.

There are per diem rates for foreign locations published monthly by the State Department. They adjust for changes in prices and exchange rates. You can download them in an Excel file. Attempting to create a lookup function using the Excel file data however is very difficult because some of the rates begin and end based upon "season" dates (for a higher rate during summer for instance) and those dates are only listed as MM/DD. In cases where the season extends from a month late in the year to one early in the next, it is in two different years. There are also effective dates, for new rates, and expiration dates, for when a rate is no longer in effect. Trying to search the table using SUMIFS for location and date seems to be impossible, mainly due to the season date issue.

The Defense Department maintains the rates for some locations as well, and they compile the GSA rates for continental U.S., the State Department foreign rates, plus their rates. And they have a "relational" file that is in XML format. But even that has the truncated season dates of just the month and day. The relational file does however appear to retain expired rates for a period of time unlike the State Department file which drops them off when a new rate is effective.

The goal would be to create an Excel file with a lookup formula, perhaps with drop-downs to select the location, that looks up the correct per diem rate for any given date. It can't go back forever, but at least a few months would be needed to give people time to return from a trip and then fill out their per diem reimbursement form. The rate would then be filled in from this lookup rather than them search the website on their own.

Then each month the new rate file would be merged into the existing file so that the latest rates would be returned. This would replace the prior download file not add more records to the existing data. (If the only way to get this to work is to combine the monthly downloads somehow into a multi-month download table, that would be less desirable, but if that is required it would be interesting to know that.)

This should be solved for the Foreign XML download file first. The foreign download contains around 3,000 rows in Excel, while the CONUS file for the United States is nearly 80,000 lines in Excel which would make the entire file a bit large to work with. And if a formula is developed on the foreign data it would likely work on the CONUS file as well.

Having worked with this and being unable to come up with a formula I wonder what use the download files are in the Excel format, if other users just manually search the data in Excel rather than use an automated lookup, which sort of defeats the purpose of having the data in Excel.

The most complex location is Sapporo Japan with four different seasons one of which extends from 12/01 - 03/14. That is a good part of the data to work with to try to develop a way to look up the per diem rate for any selected date.

The Defense Department XML files can be found at this link:
Per Diem Rate Files

If you look at this I am sure you will find it to be an interesting challenge.

If you determine that the data they provide is in fact not complete enough to do such a search, that might be helpful in getting them to research this and change the format of their downloadable file.

But it may be that if certain "assumptions" are made, based upon the date of the download file, the year for the season begin and end dates could be backed into, and then along with the effective dates, a search made.

Thank you and good luck,
David

I think this is best suited to a VBA routine with multiple functions that you manually update annually. It's possible to do this but it's a time consuming project. I'd like to offer some pointers but I won't be able to complete it for you. These folks here are itching for a go though by the looks of it...!

UPDATE:
I've downloaded an ASCII file and that is arranged already in a way that can be read by a TEXT stream input in VBA. This is exciting! That stream could easily input to a combobox and be used in a userform that you can select exactly what you want and have the rates calculate from the user selection in some way. That's your best bet.

 

[DR_iDE]

Dr. Demento made an outstanding effort. His method of first excluding rows based on effective/expiration date, then moving on to the season start and end, may correctly account for not knowing the exact year of the season dates. But it hasn't been fully tested.

An "assumption" that could perhaps be made is that for any rate change, it is a prospective change, one going forward only.

For what it is worth, if you are able to come up with another approach, or even if you would be able to test his VBA code, but only on the foreign XML file, that would be great.

And it just occurred to me that I could set a file up to test his code without setting up the drop-downs, just hard code the Canada location in along with the date to test it.

I find it hard to believe that all of the people who download these files from the government websites go to this much trouble to create a lookup on the data. Which makes me wonder, are we missing something, or is it possible no one has ever done this? All they do is use the Excel file for a CTRL F manual search?

 

[Dr. Demento]

DR_iDE,

Thank you for your kind words. It's problems like the one you posed that get me excited about testing my knowledge and exploring novel ways. It's fun!!

I will admit that I found your request somewhat puzzling -- in my branch of govt, we simply plug in our destination into a website and it does the rest. I don't think I knew about these files before your puzzler. Do other branches not have the same access/requirements? Or is there some other use I'm simply ignorant of??

Rhodie72, it didn't even occur to me to use combo boxes and userforms (an area I'm woefully ignorant in); that would be slick. I chose the sheet for entry just so that multiple entries could be made simultaneously but that's just me.

Thanks y'all.!

 

[DR_iDE]

I took over work on a file where the creator had included this lookup feature, presumably so users would not have to look up the rate themselves. But as I worked with it I realized that it was not taking into account the season dates. And upon downloading the files I realized that the State Department (what is their budget?) are providing a file that includes only the month and day. Many attempts to convert those dates into full dates in Excel which could then be compared failed.

Your method of excluding rows first based on effective and expiration dates, then moving on to a normalized season start and end date, may seem to work but it is likely that a date scenario could break that algorithm. The row passes the effective date expiration date test, but then say the selected date for lookup is 3/15/16, and the date of the file is 04/01/16, if the assumption is made that the season date range of 11-15 through 04-15 is 11-15-16 through 04-15-17, it would not include the selected date. Though I believe you said your code tests for both ranges, so also including 11-15-15 through 04-15-16 would include the selected date. But if the rate changed effective 04/05/16? I'm not getting anywhere with this, that is what computer programs are for.

But just to get back to reality, the first problem seems to be that the State Department is issuing a file that does not include the year. Then, given that fact, is it possible for the average user, they claim to have many who download the file, WITHOUT RESORTING TO EXTENSIVE CUSTOM PROGRAMMING ETC. to use the Excel file to lookup the rate? If not for doing a lookup, what is the point of having the data in Excel in the first place? To run a CTRL F search? And then have manual human interaction to choose the correct rate?

The huge size of the continental U.S. file also makes including the entire table to lookup within an Excel file less than feasible, as you noticed it is 10MB. But the foreign file alone would work. Then each month the data table would need to be updated. Your method of modifying the download also would not work since that would create more work, the idea would be to integrate the file exactly as it is downloaded, so that it could be easily replaced each month.

I have already explained to those involved that it is impossible, or nearly so, to do a lookup, that the prior file was not actually working. I added links to the websites where the users can search and print out the rate for their period of travel. Those rates are then manually added to the per diem calculation file in Excel along with other relevant information from their trip.

Fact is, since Office is so ubiquitous, the State Department, or DOD should have an Excel Add-in that users could implement that would search their database and return the chosen amount to the Excel spreadsheet formula.

Their e-mail to me explained that they would need a survey of users to determine if any changes to their file was warranted. I feel that the fact that the file is not useful due to omitting the year on the season dates is proof enough that something should be changed. Adding the year to those dates would not likely break whatever uses someone was using the file for without the year on those fields. I believe the GSA file for U.S. locations runs for a full year so this problem is not an issue in that case.

The other issue is how many months back to try to allow the downloaded file to be able to search. It seems as though on the monthly file download all prior rates are removed once a new rate is included. The DOD XML file appears to avoid this problem.

Doing a solution similar to yours would be possible in Excel formulas. To the right of the downloaded data, in the columns to the right of that, add formulas that take all the date fields and convert them to Excel dates, do both conversions prior year/current and current year/next for the season dates, and then run the comparisons for each date range against the selected date entering a 1 or 0 in a check column next to each date range, then adding up all those check columns across each row, perhaps the one single row would be the one that had the highest number. This is a totally different approach than what was in the file originally, using SUMIFS which you can run against all of the date columns simultaneously and it just returns the row that matches all of the criteria. But for that to work you would need to know which of the season ranges was correct, unless perhaps an OR statement was used to check if the date fell within either of the season date ranges - perhaps that is the answer.

But again, just to get back to reality, perhaps the goal here isn't just to solve this problem, even if it takes a Cray Supercomputer to do it, the point is it should not take that much effort in the first place.

 

[Dr. Demento]

You've obviously put a lot of effort into this

A couple of thoughts:

• The only time the 365 days are added is when the Start Date > End Date. The example you provide does not meet that stipulation and therefore the sample date would be captured appropriately.
• Excel reads the month/day as using the current year; using the function dhCNumdate and FORMAT, each Start and End date are converted into "real dates." The end result is similar to your suggestion of helper columns.
• Importing data without manual manipulation only has two solutions - create a macro to do the moving for you (Macro Recorder would work, as the imported data would be the same every time) or create two separate workbooks for CONUS and OCONUS.

Best wishes.

 

[Dr. Demento]

FYI -- given the size of the drop down lists (if you choose to add Data Validation to the data entry cells), here's an awesome article on
How to Search Data Validation Drop-down Lists in Excel.

 

[DR_iDE]

The file that I took over, that included the per diem rate lookup, also included cascading drop-downs. I was able to get the drop-downs working. The problem with Excel in a scenario like this where you are trying to create a file that is sent out to various users, many of whom are non-technical, is that the protection options are extremely lame. It is surprising that Microsoft never beefed up the protection in Excel. If you protect the cells with the drop-downs - THE USER CAN'T "CHANGE" THE CELL BY SELECTING A VALUE FROM THE DROP-DOWN! And if you don't protect the drop-down cell, THE USER CAN JUST PASTE A BLANK CELL RIGHT OVER IT ELIMINATING IT ALTOGETHER. It seems like the only option would be to use VBA events to catch what users are doing and prevent them from doing certain things.

The main task for anyone reading this thread remains - find the simplest most straightforward way to run a search against the DOD XML download of per diem rates, Foreign file only (the same technique should be able to be adapted to the U.S. file later) and if at all possible do this with formulas only. VBA is great if that is the only way, but a non-macro solution would be preferable in this case.

I still believe the month and day only for Season begin and end may be making this impossible. As time allows I will fully test your VBA solution.

The decision makers in our organization are now considering a program called Concur - and apparently it updates rates annual for U.S. and monthly for Foreign. I would love to know how they are handling the season date issue. Anyone with access to Concur could test some dates for Sapporo Japan and try to trip it up by selecting dates around the season start and end etc. Concur may have one of their staff fully massage the data from DOD each month, it would be worth it for them to spend the time since they are making it part of their commercial product.