Forum Discussion

Blake753's avatar
Blake753
Icon for Helper II rankHelper II
6 years ago
Solved

Group Dates into 2 week intervals

I have a set of data with dates starting on 1/5/2020.  I need to group these dates in a column into 2 week intervals with the last Saturday date being the return value.  These dates always begin on a...
  • amitchandak's avatar
    amitchandak
    6 years ago

    Blake753 , Try like

     

    Week end date = date(2020,1,5) + (Quotient(datediff(date(2020,1,5),[Date],day),14)+1)*14
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Blake753 , You can divide into week like this

 

2 Week No = Quotient(datediff(date(2020,1,5),[Date],day),14)+1

 

Week end date = date(2020,1,5) + (Quotient(datediff(date(2020,1,5),[Date],day),14)+2)*7

  • Blake753's avatar
    Blake753
    Icon for Helper II rankHelper II
    6 years ago

    That didn't quite seem to work, here are the results I'm seeing:

    amitchandak 

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Blake753 , Try like

       

      Week end date = date(2020,1,5) + (Quotient(datediff(date(2020,1,5),[Date],day),14)+1)*14
      • Blake753's avatar
        Blake753
        Icon for Helper II rankHelper II
        6 years ago

        amitchandak I had to change the start date to January 4, 2020 as it was one day off but it seems to have worked, thank you very much