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)


      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('week', INT( SPLIT([Delivery date]," ",2) ) -1,
      DATE( "1/1/" + SPLIT([Delivery date]," ",3)))

      Thanks in advance,