2 Replies Latest reply on Mar 10, 2018 9:44 AM by Hima vardhan Reddy Pavuluri

    Convert string "week xx yyyy" to date yyyy-mm-dd (the Sunday)

    michelle.pace

      Hello, I have the column "Delivery Week" which contains the below strings:

       

      Delivery Week

      week 1 2018    

      week 2 2018

      week 22 2018

       

      I need to make a calculation which returns the DATE of the Sunday of each of the weeks. For example, "week 2 2018" >>> to return as:  2018-01-14. I have the following code which seems to work but surely there has to be a simpler way?

       

      DATEADD('day',6,
      DATEADD('week', INT( SPLIT([Delivery date]," ",2) ) -1,
      DATE( "1/1/" + SPLIT([Delivery date]," ",3)))
      )

      Thanks in advance,

      Michelle