0 Replies Latest reply on Jan 22, 2014 7:19 PM by naveen.murali

    Calculate number of working days between a custom date range

    naveen.murali

      I have two data sources

      • One is an excel file which maintains a list of working days like below:

      dates.png

      • Another is a SQL database which contains all my invoice data (which has lets say the columns "Invoice date", "Sales $" )


      Requirement

      • Provide the user the ability to choose two dates(say- 09/01/2013 and 09/29/2013) and view all sales dollars between two custom date ranges (which comes from my SQL data base)
      • We want to be able to display the number of working days between the same two date ranges (in our case 09/01/2013 and 09/29/2013) [[which comes from my excel file]]

       

      Problem

      • The problem is that let us say I have invoices only on 8 days between 09/01/2013 and 09/29/2013 -- My number of working days shows up as 8 instead of "20".
        • It seems like if there was a way to display all dates between 09/01/2013 and 09/29/2013 irrespective of whether there was an invoice or not and display "0's" on days where there was no invoice-- that would solve my problem?

      Here is a screenshot

      dates.png

       

      Here is a screenshot of how it looks when I have drilled down to the day level

       

      dates.png

       

      Any insights on how I can display the correct number of working days?

       

      Thanks!

      Naveen