0

To calculate time between dates of weekdays only.

Can I calculate the time between two dates including only weekdays for e.g. 1/9/15 2:23:40 PM   and  1/15/15 4:06:10 PM between these two dates if I use timebetween(D1,D2) this will include Sat and Sun also. I just want weekdays time. Can anyone guide me.

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • I was playing around with the date data provided by Incorta. I had to set the date field function to "key" to get this to work. The date schema has a field called work_day_seq. This is a count of the workdays excluding weekends (holidays are NOT excluded.) I then used the lookup function in a new formula to get the difference between a date.work_day_seq tied to a different date field in another table with the lookup result. The lookup function was only testing against a fixed date string of 2020-01-01 but could use any other date field or a presentation variable. The test function looks like this: 

    Date_Time.Date.work_day_seq - lookup(
        Date_Time.Date.work_day_seq,
        Date_Time.Date.Date,
        date(
            "2020-01-01"
        )
    )

    I have attached a picture of a test table showing this with just the date table:

    Like
  • Root_08012020 you may also find some helpful details in this Q&A thread: https://community.incorta.com/t/h44ttc/business-days-between

    Like
    • Dustin Basil The video was useful but did not address the direct question of the difference. Counting days is cool, but is there a better way to do the difference from what I suggested?

      Like
    • Marc Paige offhand I do not, I was looking to make sure Root_08012020 had the foundational reference necessary to implement your solution or craft their own.

      Like
  • Marc Paige another way of doing the same scenario in Incorta:

    - https://youtu.be/Ep7ZUeS5UX8

    This videos shows how to  calculate number of working days between two dates using date dimension and joining transaction table with date and date alias table.

     

    Other videos in Youtube playlist:

    https://www.youtube.com/watch?v=LG6ArSCstL0&list=PLhaCz3hHIgcINS0QQjj05JWOTOsmdXTb3

    Like
Like Follow
  • 8 mths agoLast active
  • 5Replies
  • 80Views
  • 5 Following

Product Announcement


We are happy to
announce Incorta 4.8 !!!