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

 

[Roderick_E]

I'm afraid to get involved with the corrupt state department

 

[Dr. Demento]

Oooh, this sounds like twisted fun!! When I get on my corrupt state computer tomorrow,I'll give it a go.

I'm thinking UDF using arrays might be the way to go.

 

[Dr. Demento]

I'm still working this. One thought that occurred is to use Autofilter. Going to try that first. Do you have a suspense for this??

 

[DR_iDE]

I'm still working this. One thought that occurred is to use Autofilter. Going to try that first. Do you have a suspense for this??

Thank you for looking at this.

I an not sure what you mean by suspense. Do you mean a working file in Excel where I attempted to get this working?

I tried converting all the date columns to Excel dates, then attempted to create the date ranges using those, with the idea that once valid dates were associated with each location/date combination, a simple SUMIFS formula would be able to find the matching rate given the location and date entered.

But since they only provide month and day, it complicates it, perhaps even makes it impossible - at least in those cases where the season start date is 'after' the season end date, i.e. 11/15/YY through 04/10/YY+1. The SUMIFS method does seem to work where there are no seasons, it just requires making sure the effective date is also taken into account.

I contacted the State Department and they indicated that they have never had this question in ten years, and that other users download the file all the time. I don't know what the other users are doing with the file, perhaps they don't automate it, they just use it for a manual search, where human interaction is required to look at the location and pick the date range. That of course defeats the purpose of having the data in Excel in the first place.

It is my belief that the file they are providing for download should be complete enough for even the average Excel user to use simple formulas to create some sort of useful lookup. It should not require extensive VBA programming or other manipulations. But I'm not sure this is possible with the file they provide even with VBA or other extraordinary formulas.

I am hoping that someone looking at this issue may find a way to do this, but if not, at least I will know it was not just me who could not figure it out.

The contact at the State Department indicated that they would need a survey of their users to determine any need for changes, and how the file is currently being used, etc.

In addition to providing a download that provides all the date information necessary to do a simple lookup, it seems like they would be able to provide an ADD-IN of some sort that users could integrate into their Office applications and get the per diem rate from that based upon the location and date selected.

Thanks again,
David

 

[Dr. Demento]

David,

Sorry - 'suspense' is government-speak for 'deadline' -- I didn't know if you needed this by a specific date.

Unfortunately, my knowledge of Excel formulas is much more limited than my programming abilities, so while I was able to produce a VBA solution, I cannot give any insight how to do this with solely formulas; apologies.

A few notes on my approach:

1. I used the DoD .xml data source (Relational link); I used/tested the CONUS & OCONUS data.
2. I imported the .xml data into Sheet1 into a table (instructions)
   1. I added the OCONUS data as well, placing data in identical columns as CONUS data; there were a number of blank columns, including the rate Expiration Date, which I co-opted a date for in the code.
3. I didn't use any error trapping in the code because I used Data Validation with dependent dropdowns for the State/Country and City/Post. I used data validation for the date as well.
4. Sheet2 contained the input and output
   1. Range("A:D") contained data for Global (CONUS vs OCONUS) | State | City | Trip Date (input)
   2. Range("E:J") contained the various rates and max_per_diem (output)

My approach was to eliminate all the "irrelevant" entries using AutoFilter for Global, State, City, and Trip Date. After running the sub, only one row with the relevant data should be visible. I have not conducted extensive testing, but the cursory review, it seems to work. The number in column K is the number of entries that the macro filtered for each location; it should always be one (if not, there are exceptions that I didn't foresee).

One caution - there are a number of fields that are redundant or superfluous (and they offend my sense of tidiness ); I attempted to hide them but the cost data wasn't captured properly. So, you can either leave the useless fields alone, delete them, or if you have a work-around that allows you to hide them but get the proper output, I'd be very interested.

I have two subs - Main sub and some accessory/helper functions.

hth

Dr. D

Main code

Option Explicit

Sub autofilter_PerDiem()
' ~~ U.S. government per diem rate table - season dates month and day only
' [URL]http://www.mrexcel.com/forum/excel-questions/970034-u-s-government-per-diem-rate-table-season-dates-month-day-only.html[/URL]
' DoD .xml data source  (Relational link) ||  [URL="http://www.defensetravel.dod.mil/site/perdiemFiles.cfm"]Per Diem Rate Files[/URL]
' Import .xml data into table || [URL="http://www.jkp-ads.com/Articles/XMLAndExcel05.asp"]Excel: XML and Excel: XML in Excel[/URL]

Dim wbk As Workbook
  Set wbk = ActiveWorkbook
Dim shtSrc As Worksheet
  Set shtSrc = wbk.Sheets("Sheet1")
Dim shtDest As Worksheet
  Set shtDest = wbk.Sheets("Sheet2")
Dim tbl As ListObject
  Set tbl = shtSrc.ListObjects(1)
Dim rngtbl As Range
  Set rngtbl = tbl.Range
Dim rngSrc As Range, _
    rngData As Range, _
    cell As Range, _
    data As Range
  Set rngData = rng_Used(shtDest)
  Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1, 3)  ' ~~ Confines rngData to first three columns of Sheet2 (excludes header)

Dim colCountry As Long, _
    colState As Long, _
    colCity As Long, _
    colEffDate As Long, _
    colExpDate As Long, _
    colStartDate As Long, _
    colEndDate As Long
  colCountry = col_find("conus_ind", rngtbl.Rows(1))
  colState = col_find("state_name", rngtbl.Rows(1))
  colCity = col_find("location_name", rngtbl.Rows(1))
  colEffDate = col_find("eff_date", rngtbl.Rows(1))
  colEffDate = tbl.ListColumns("eff_date").Index
  colExpDate = col_find("exp_date", rngtbl.Rows(1))
  colStartDate = col_find("start_date", rngtbl.Rows(1))
  colEndDate = col_find("end_date", rngtbl.Rows(1))
  
Dim colLodging As Long, _
    colLocal_meals As Long, _
    colMeals_rate_only As Long, _
    colProp_meals_rate As Long, _
    colIncidentals As Long, _
    colMax_per_diem As Long
  colLodging = col_find("lodging_rate", rngtbl.Rows(1))
  colLocal_meals = col_find("local_meals", rngtbl.Rows(1))
  colMeals_rate_only = col_find("meals_rate_only", rngtbl.Rows(1))
  colProp_meals_rate = col_find("proportional_meals_rate", rngtbl.Rows(1))
  colIncidentals = col_find("incidentals", rngtbl.Rows(1))
  colMax_per_diem = col_find("max_per_diem", rngtbl.Rows(1))
  colMax_per_diem = tbl.ListColumns("max_per_diem").Index

Dim StartDate As Long, _
    EndDate As Long, _
    TripDate As Long, _
    lngEntries As Long, _
    cntr As Long
Dim strTripCountry As String, _
    strTripState As String, _
    strTripCity As String

  For Each data In rngData.Rows
    strTripCountry = shtDest.Cells(data.Row, 1).Value
    strTripState = shtDest.Cells(data.Row, 2).Value
    strTripCity = shtDest.Cells(data.Row, 3).Value
    TripDate = shtDest.Cells(data.Row, 4).Value2
  
    With shtSrc
      AutoFilter_ShowAll shtSrc, wbk  ' ~~ Turn off AutoFilter (if presently on)
      
      ' ~~ Hide rows based on Country, State, and City
      tbl.Range.AutoFilter Field:=colCountry, _
                       Criteria1:=strTripCountry
      tbl.Range.AutoFilter Field:=colState, _
                       Criteria1:=strTripState
      tbl.Range.AutoFilter Field:=colCity, _
                       Criteria1:="=" & strTripCity

      Set rngSrc = rng_Used(shtSrc).Rows.SpecialCells(xlCellTypeVisible)  ' ~~ Update range to visible cells only || [URL="http://stackoverflow.com/a/1370431"]How to get the range of the visible rows after applying an advanced filter in Excel (VBA) - Stack Overflow[/URL]
      ' ~~ Evaluate if TripDate falls between Effective and Expiration Dates; if FALSE, then hide row
      For Each cell In rngSrc.Cells.Rows
        If cell.Row <> 1 Then
          ' ~~ Since OCONUS locations do not have Expiration Date for rates, _
                force expiration date to be Effective Date + 1 year
          If .Cells(cell.Row, colExpDate) = vbNullString Then
            .Cells(cell.Row, colExpDate) = .Cells(cell.Row, colEffDate) + 365
            If .Cells(cell.Row, colEffDate) <= TripDate And _
               .Cells(cell.Row, colExpDate) >= TripDate Then
              Else
              .Rows(cell.Row).EntireRow.Hidden = True
            End If
            .Cells(cell.Row, colExpDate) = vbNullString  ' ~~ remove forced expiration date
          End If
        End If
      Next cell
  
      Set rngSrc = rng_Used(shtSrc).Rows.SpecialCells(xlCellTypeVisible)  ' [URL="http://stackoverflow.com/a/1370431"]How to get the range of the visible rows after applying an advanced filter in Excel (VBA) - Stack Overflow[/URL]
      ' ~~ Evaluate if TripDate falls between Start and End Dates; if FALSE, then hide row
      For Each cell In rngSrc.Cells.Rows
        If cell.Row <> 1 Then
          StartDate = dhCNumdate(Format(.Cells(cell.Row, colStartDate), "MMDDYYYY"), "MMDDYYYY")
          EndDate = dhCNumdate(Format(.Cells(cell.Row, colEndDate), "MMDDYYYY"), "MMDDYYYY")
          ' ~~ If EndDate is after 12/31, add 365 days to EndDate to force EndDate into next calendar year
          If StartDate > EndDate Then _
            EndDate = EndDate + 365
    
          If StartDate <= TripDate And _
             EndDate <= TripDate Then _
              .Rows(cell.Row).EntireRow.Hidden = True
        End If
      Next cell
      
      ' ~~ After all non-pertinent rows are hidden, remaining non-header row will contain desired information _
           [.Areas(shtSrc.UsedRange.SpecialCells(xlCellTypeVisible).Areas.Count)] will ensure that the last area/data range is captured, _
            even if non-relevant columns are hidden by the user
      Set rngSrc = shtSrc.UsedRange.SpecialCells(xlCellTypeVisible).Areas(shtSrc.UsedRange.SpecialCells(xlCellTypeVisible).Areas.Count)  ' [URL="http://www.ozgrid.com/forum/showthread.php?t=23611#2"]Working with visible cells of filtered data[/URL]
      lngEntries = shtSrc.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count  ' ~~ Subtract one to remove header row from count
      
      ' ~~ Write data to destination sheet
      With rngSrc
        shtDest.Cells(data.Row, 5) = Format(.Cells(1, colLodging), "$#,##0.00;[Red]$#,##0.00")    ' [URL="http://stackoverflow.com/a/20648352"]What are .NumberFormat Options In Excel VBA? - Stack Overflow[/URL]
        shtDest.Cells(data.Row, 6) = Format(.Cells(1, colLocal_meals), "$#,##0.00;[Red]$#,##0.00")
        shtDest.Cells(data.Row, 7) = Format(.Cells(1, colMeals_rate_only), "$#,##0.00;[Red]$#,##0.00")
        shtDest.Cells(data.Row, 8) = Format(.Cells(1, colProp_meals_rate), "$#,##0.00;[Red]$#,##0.00")
        shtDest.Cells(data.Row, 9) = Format(.Cells(1, colIncidentals), "$#,##0.00;[Red]$#,##0.00")
        shtDest.Cells(data.Row, 10) = Format(.Cells(1, tbl.ListColumns("max_per_diem").Index), "$#,##0.00;[Red]$#,##0.00")
        shtDest.Cells(data.Row, 11) = lngEntries  ' ~~ Count how many entries were identified that met criteria (should be only 1) _
                                                          Comment this line out once satisfied that program yielding expected results
      End With
    End With
  Next data
  
End Sub

Accessory/helper mods

Option Explicit
Public Function col_find(rts As String, _
                         gnr As Range) As Long
  col_find = gnr.Find(What:=rts, _
                      LookIn:=xlValues, _
                      LookAt:=xlPart, _
                      SearchOrder:=xlByColumns, _
                      SearchDirection:=xlNext, _
                      MatchCase:=False, _
                      SearchFormat:=False).Column
End Function

Public Function AutoFilter_ShowAll(ths As Worksheet, _
                                   Optional kbw As Workbook)
' [URL="http://stackoverflow.com/a/23462661"]Excel 2013 VBA Clear All Filters macro - Stack Overflow[/URL]
On Error Resume Next
  If kbw Is Nothing Then _
    kbw = ActiveWorkbook
  Set ths = kbw.Sheets(ths)
  
    ths.ShowAllData
    ths.ListObjects(1).AutoFilter.ShowAllData
On Error GoTo 0
End Function

Public Function dhCNumdate(ByVal lngdate As Long, _
                           ByVal strFormat As String) As Variant
' Convert numbers to dates, depending on the specified format
' and the incoming number. In this case, the number and the
' format must match, or the output will be useless.
' [URL]https://msdn.microsoft.com/en-us/library/aa227484(v=vs.60).aspx[/URL]
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim fOK As Boolean
  fOK = True
  
  Select Case strFormat
    Case "MMDDYY"
      intYear = lngdate Mod 100
      intMonth = lngdate \ 10000
      intDay = (lngdate \ 100) Mod 100
    Case "MMDDYYYY"
      intYear = lngdate Mod 10000
      intMonth = lngdate \ 1000000
      intDay = (lngdate \ 10000) Mod 100
    Case "DDMMYY"
      intYear = lngdate Mod 100
      intMonth = (lngdate \ 100) Mod 100
      intDay = lngdate \ 10000
    Case "DDMMYYYY"
        intYear = lngdate Mod 10000
        intMonth = (lngdate \ 10000) Mod 100
        intDay = lngdate \ 1000000
    Case "YYMMDD", "YYYYMMDD"
      intYear = lngdate \ 10000
      intMonth = (lngdate \ 100) Mod 100
      intDay = lngdate Mod 100
    Case Else
      fOK = False
  End Select
  
  If fOK Then
    dhCNumdate = DateSerial(intYear, intMonth, intDay)
  Else
    dhCNumdate = Null
  End If
End Function

'#####################################################################
' ~~ rng_Used  : Function to Identify the range that actually
'    contains data in the ths Worksheet Object. The Active Sheet is
'    used if the ths argument is not specified. Also, you may choose
'    to set the StartAtA1 argument to true, if you'd like to include the
'    blank rows and columns in the beginning of the worksheet.
'#####################################################################
'Author     : Ejaz Ahmed
'Email      : [EMAIL="StrugglingToExcel@outlook.com"]StrugglingToExcel@outlook.com[/EMAIL]
'Date       : 26 May 2013
'Website    : [URL]http://strugglingtoexcel.com/2014/05/26/actual-used-range-excel-vba/[/URL]
'#####################################################################\
Public Function rng_Used(Optional ByVal ths As Worksheet, _
                         Optional ByVal StartAtA1 As Boolean = False, _
                         Optional ByVal gnrStart As Range, _
                         Optional ByVal nlbAll As Boolean = True) As Range
Dim cellFirst As Range
Dim cellLast As Range
   
  If ths Is Nothing Then
      Set ths = Application.ActiveSheet
  End If
   
  ' ~~ Find last cell on page or last cell before blank row (exclude data AFTER blank row)
  If nlbAll = True Then
    Set cellLast = LastCellInSheet(ths)
  ElseIf nlbAll = False Then
    Set cellLast = LastCellBeforeBlankRow(ths)
  End If
  
  ' ~~ Identify the First Cell
  If StartAtA1 = True Then
      Set cellFirst = ths.Cells.Item(1, 1)
  ElseIf StartAtA1 = False Then
      If gnrStart Is Nothing Then
        Set cellFirst = FirstCellInSheet(ths)
      Else
        Set cellFirst = ths.Range(gnrStart).Item(1, 1)
      End If
  End If
   
  'Return the Used Range
  Set rng_Used = Range(cellFirst, cellLast)
 
End Function
'#####################################################################
'FirstCellInSheet  : Function to Identify the First Cell in the used
'used range of a worksheet that contains data.
'#####################################################################
'Author     : Ejaz Ahmed
'Email      : [EMAIL="StrugglingToExcel@outlook.com"]StrugglingToExcel@outlook.com[/EMAIL]
'Date       : 26 May 2013
'Website    : [URL]http://strugglingtoexcel.com/2014/05/26/actual-used-range-excel-vba/[/URL]
'#####################################################################
Public Function FirstCellInSheet(ByRef ths As Worksheet, _
                                 Optional ByRef gnrStart As Range = 1, _
                                 Optional ByRef worFirst As Long = 1, _
                                 Optional ByRef locFirst As Long = 1) As Range
                                  ' ~~ set first row/col to A1, if not provided by user
'Declare Function Level Variables
Dim rngTemp As Range
   
  'Get the first row that has data by setting the search direction
  'to Next and search order to by-Rows
  If ths.Cells(1, 1).Value = vbNullString Then
      Set rngTemp = ths.Cells.Find("*", _
          , xlFormulas, xlPart, xlByRows, xlNext)
      If Not rngTemp Is Nothing Then worFirst = rngTemp.Row
      'Get the last column that has data by setting the search direction
      'to Previous and search order to by-Columns
      Set rngTemp = ths.Cells.Find("*", _
          , xlFormulas, xlPart, xlByColumns, xlNext)
      If Not rngTemp Is Nothing Then locFirst = rngTemp.Column
  End If
   
  'Return the First Cell
  Set FirstCellInSheet = ths.Cells.Item(worFirst, locFirst)
 
End Function
'#####################################################################
'LastCellInSheet  : Function to Identify the Last Cell in the used
'used range of a worksheet that contains data.
'#####################################################################
'Author     : Ejaz Ahmed
'Email      : [EMAIL="StrugglingToExcel@outlook.com"]StrugglingToExcel@outlook.com[/EMAIL]
'Date       : 26 May 2013
'Website    : [URL]http://strugglingtoexcel.com/2014/05/26/actual-used-range-excel-vba/[/URL]
'#####################################################################
Public Function LastCellInSheet(ByRef ths As Worksheet) As Range
 
'Declare Function Level Variables || Initialize default values
Dim rngTemp As Range
Dim rowLast As Long: rowLast = 1
Dim colLast As Long: colLast = 1
   
  'Get the last row that has data by setting the search direction _
   to Previous and search order to by-Rows
  Set rngTemp = ths.Cells.Find("*", _
      , xlFormulas, xlPart, xlByRows, xlPrevious)
  If Not rngTemp Is Nothing Then rowLast = rngTemp.Row
  'Get the last column that has data by setting the search direction _
   to Previous and search order to by-Columns
  Set rngTemp = ths.Cells.Find("*", _
      , xlFormulas, xlPart, xlByColumns, xlPrevious)
  If Not rngTemp Is Nothing Then colLast = rngTemp.Column
   
  'Return the Last Cell
  Set LastCellInSheet = ths.Cells.Item(rowLast, colLast)
 
End Function
Private Function LastCellBeforeBlankRow(ByRef ths As Worksheet) As Range
 
'Declare Function Level Variables || Initialize default values
Dim rngTemp As Range
Dim rowLast As Long: rowLast = 1
Dim colLast As Long: colLast = 1
   
  'Get the last row that has data by setting the search direction _
   to Previous and search order to by-Rows
  Set rngTemp = ths.Cells.Find("", _
      , xlValues, xlWhole, xlByRows, xlNext)
  If Not rngTemp Is Nothing Then rowLast = rngTemp.Row
  'Get the last column that has data by setting the search direction _
   to Previous and search order to by-Columns
  Set rngTemp = ths.Cells.Find("*", _
      , xlFormulas, xlPart, xlByColumns, xlPrevious)
  If Not rngTemp Is Nothing Then colLast = rngTemp.Column
   
  'Return the Last Cell
  Set LastCellBeforeBlankRow = ths.Cells.Item(rowLast, colLast)
 
End Function

 

[DR_iDE]

Wow, that looks fantastic. I look forward to giving it a test.

David

 

[Dr. Demento]

Let me know if you run into any problems. I would have uploaded it, but 1) Mr Excel doesn't have that capability and 2) the combined CONUS/OCONUS files came in just under 10MB (with some redundant data).

Good luck, David

 

[DR_iDE]

Taking just the OCONUS file and removing all but Canada and Japan would leave 731 records that includes several examples of the problem date scenario, that being those cases where the season begin date and end date span year end 12/31. I created a table of these but after attempting to copy them into the forum table setup, I noticed that pasting it back into Excel resulted in all of the "mm/dd" fields taking on the year 2016, which is what Excel will do, as you know.

There are 93 rows from the Canada and Japan data after the rows are removed that don't have the problematic date scenario.

Choosing a date of travel that falls within the effective date, expiration date, start_date and end_date for these 93 rows is really the acid test for any solution.

Thank you for all your help. It really is appreciated.

David

Here are the 93 rows, sorry about the awkward table size:

country_name location_name linked_location_name eff_date exp_date start_date end_date lodging_rate meals_rate_only incidentals max_per_diem JAPAN CAMP BUTLER USMCB OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP BUTLER USMCB OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP BUTLER USMCB OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP BUTLER USMCB OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN CAMP COURTNEY OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP COURTNEY OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP COURTNEY OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP COURTNEY OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN CAMP FOSTER USMC OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP FOSTER USMC OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP FOSTER USMC OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP FOSTER USMC OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN CAMP HANSEN USMC OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP HANSEN USMC OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP HANSEN USMC OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP HANSEN USMC OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN CAMP KINSER USMC OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP KINSER USMC OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP KINSER USMC OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP KINSER USMC OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN CAMP LESTER OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP LESTER OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP LESTER OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP LESTER OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN CAMP SCHWAB USMC OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP SCHWAB USMC OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP SCHWAB USMC OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP SCHWAB USMC OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN CAMP SHIELDS OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN CAMP SHIELDS OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN CAMP SHIELDS OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN CAMP SHIELDS OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN FUTENMA MCAS OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN FUTENMA MCAS OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN FUTENMA MCAS OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN FUTENMA MCAS OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN KADENA AB OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN KADENA AB OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN KADENA AB OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN KADENA AB OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN KADENA NAF OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN KADENA NAF OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN KADENA NAF OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN KADENA NAF OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN TORI STATION OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN TORI STATION OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN TORI STATION OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN TORI STATION OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN US NAVAL HOSPITAL, OKINAWA OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN US NAVAL HOSPITAL, OKINAWA OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN US NAVAL HOSPITAL, OKINAWA OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN US NAVAL HOSPITAL, OKINAWA OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 JAPAN WHITE BEACH NAVAL FACILITY OKINAWA PREFECTURE 04/01/2016 04/30/2016 09/15 04/30 162 82 20 264 JAPAN WHITE BEACH NAVAL FACILITY OKINAWA PREFECTURE 05/01/2016 07/31/2016 09/15 04/30 169 85 21 275 JAPAN WHITE BEACH NAVAL FACILITY OKINAWA PREFECTURE 08/01/2016 12/31/2049 09/15 04/30 181 90 23 294 JAPAN WHITE BEACH NAVAL FACILITY OKINAWA PREFECTURE 10/01/2015 03/31/2016 09/15 04/30 154 79 19 252 CANADA VANCOUVER 01/01/2016 03/31/2016 09/16 05/14 141 80 20 241 CANADA VANCOUVER 04/01/2016 04/30/2016 09/16 05/14 145 83 21 249 CANADA VANCOUVER 05/01/2016 07/31/2016 09/16 05/14 153 87 22 262 CANADA VANCOUVER 08/01/2016 12/31/2049 09/16 05/14 149 84 21 254 CANADA CALGARY 01/01/2016 03/31/2016 10/01 04/30 194 67 17 278 CANADA CALGARY 04/01/2016 04/30/2016 10/01 04/30 200 70 17 287 CANADA CALGARY 05/01/2016 07/31/2016 10/01 04/30 211 74 18 303 CANADA CALGARY 08/01/2016 12/31/2049 10/01 04/30 204 71 18 293 CANADA QUEBEC 01/01/2016 03/31/2016 10/01 04/30 155 74 19 248 CANADA QUEBEC 04/01/2016 04/30/2016 10/01 04/30 160 76 19 255 CANADA QUEBEC 05/01/2016 07/31/2016 10/01 04/30 169 81 20 270 CANADA QUEBEC 08/01/2016 12/31/2049 10/01 04/30 164 79 19 262 CANADA PRINCE EDWARD ISLAND 01/01/2016 03/31/2016 10/01 05/31 126 79 19 224 CANADA PRINCE EDWARD ISLAND 04/01/2016 04/30/2016 10/01 05/31 130 81 20 231 CANADA PRINCE EDWARD ISLAND 05/01/2016 07/31/2016 10/01 05/31 137 86 21 244 CANADA PRINCE EDWARD ISLAND 08/01/2016 12/31/2049 10/01 05/31 133 82 21 236 CANADA RICHMOND 01/01/2016 03/31/2016 10/16 04/30 121 65 16 202 CANADA RICHMOND 04/01/2016 04/30/2016 10/16 04/30 125 67 17 209 CANADA RICHMOND 05/01/2016 07/31/2016 10/16 04/30 132 70 18 220 CANADA RICHMOND 08/01/2016 12/31/2049 10/16 04/30 128 68 17 213 CANADA SIDNEY 01/01/2016 03/31/2016 10/16 04/30 92 74 19 185 CANADA SIDNEY 04/01/2016 04/30/2016 10/16 04/30 94 76 19 189 CANADA SIDNEY 05/01/2016 07/31/2016 10/16 04/30 100 81 20 201 CANADA SIDNEY 08/01/2016 12/31/2049 10/16 04/30 97 79 19 195 CANADA VICTORIA 01/01/2016 03/31/2016 10/16 04/30 92 74 19 185 CANADA VICTORIA 04/01/2016 04/30/2016 10/16 04/30 94 76 19 189 CANADA VICTORIA 05/01/2016 07/31/2016 10/16 04/30 100 81 20 201 CANADA VICTORIA 08/01/2016 12/31/2049 10/16 04/30 97 79 19 195 JAPAN SAPPORO 04/01/2016 04/30/2016 12/01 03/14 189 83 21 293 JAPAN SAPPORO 05/01/2016 07/31/2016 12/01 03/14 197 87 22 306 JAPAN SAPPORO 08/01/2016 12/31/2049 12/01 03/14 211 93 23 327 JAPAN SAPPORO 10/01/2015 03/31/2016 12/01 03/14 179 79 19 277 <tbody> </tbody>

<tbody>
</tbody>

 

[DR_iDE]

Just thinking out loud a bit here, looking at some of your code.

Not sure what you mean by no expiration date - both XML download files have the expiration date:

' ~~ Since OCONUS locations do not have Expiration Date for rates, _
force expiration date to be Effective Date + 1 year

Next, are you first converting both the start and end mm/dd date to the current year so that you can then compare them?

So for instance:
Start date 10/15 to End date 3/15
This gets changed to 10/15/16 to 3/15/17?

But unless the date of the file download, and the effective date are taken into account, it could be that the correct date range should be 10/15/15 through 3/15/16

And then assuming that both calculations are made to get two possible date ranges, and the travel date tested against both, would the inclusion of the effective and expiration dates result in choosing the correct one, either the prior year/current year or the current year/next year rate?

Or what about working just from the data in the download file, can the other dates available (effective date, expiration date and even perhaps the date the file is downloaded/created) be used to somehow determine which of the two possibilities is actually the correct date range for any given row in these circumstances?

What if in the February file downloaded from the DOD the rate is changed effective 02-15-XX, then in that case the correct range to apply the new rate would be the 10-15-year minus 1 through 3-15-current year.

' ~~ Evaluate if TripDate falls between Start and End Dates; if FALSE, then hide row
For Each cell In rngSrc.Cells.Rows
If cell.Row <> 1 Then
StartDate = dhCNumdate(Format(.Cells(cell.Row, colStartDate), "MMDDYYYY"), "MMDDYYYY")
EndDate = dhCNumdate(Format(.Cells(cell.Row, colEndDate), "MMDDYYYY"), "MMDDYYYY")
' ~~ If EndDate is after 12/31, add 365 days to EndDate to force EndDate into next calendar year
If StartDate > EndDate Then _
EndDate = EndDate + 365 (Could the correct range be the Start Date - 365 to the End Date, is there really any way to know, can the other dates available be used to back into the correct choice here?)

If StartDate <= TripDate And _
EndDate <= TripDate Then _
.Rows(cell.Row).EntireRow.Hidden = True
End If
Next cell

---
Taking into account the download date of the file would require adding information to the XML data, a field labeled Data_Date perhaps, which would record the date of the file on each record, and then allow for more precise filtering.

Thanks again,
David