locked
Calculated Date Range in Lookup Column RRS feed

  • Question

  • I want to have a lookup column on a list called Report Week.  The report week is a date range between Sun and Sat.  I could easily make a column of ranges in a seperate list and then use it as a lookup but I am hoping for something more elegant.  What I envision is the user selecting the Date Range lookup in a list item and the date ranges shown are the previous, current and upcoming weeks.  So the dropdown list would look like this:

    09/20/14 - 09/26/14

    09/27/14 - 10/03/14

    10/04/14 - 10/10/14

    Can this be done w/out using .NET?  Thanks in advance.

    

    Friday, September 26, 2014 7:23 PM

Answers

  • Thanks for the info Scott.  I could not find any "dayofweek" functions in SP and the DateAdd seemed to require a different format from what you wrote.  What I ended up doing was using the date of the item task to determine the date range using a single calculated column and then I did not need to use a drop down box or seperate list to hold calculated values.  The user user enters a date that a task was performed (TaskDate) and the following equation adds the correct date range to the Report Week field:

    CONCATENATE(TEXT(TaskDate - WEEKDAY(TaskDate) + 1, "mm/dd/yy", " - ", TEXT(TaskDate - WEEKDAY(TaskDate) + 7, "mm/dd/yy"))

    This is working perfectly.

    Ken

     
    • Marked as answer by Victoria Xia Thursday, October 2, 2014 7:42 AM
    Wednesday, October 1, 2014 8:47 PM

All replies

  • more or less, you need three calculated columns (well, you may be able to do it in just the one, but it'll be obnoxious to write the formulas)...

    - start date

      => DateAdd([date field], 0 - (dayofweek([datefield]))
            basically, subtract the day of week from the reference date... this should give you the sunday that starts the week

    - end date

      => DateAdd([date field], 7 - (dayofweek([datefield]))
            basically the same, except this adds the remaining number of days in the week, to give you the saturday that ends the week

    - range (description)

      => concatenate([start date], " - ", [end date])

    you may need to fiddle with the formulas a little bit... run them in excel to tweak them.


    Scott Brickey
    MCTS, MCPD, MCITP
    www.sbrickey.com
    Strategic Data Systems - for all your SharePoint needs

    Monday, September 29, 2014 1:31 PM
  • Thanks for the info Scott.  I could not find any "dayofweek" functions in SP and the DateAdd seemed to require a different format from what you wrote.  What I ended up doing was using the date of the item task to determine the date range using a single calculated column and then I did not need to use a drop down box or seperate list to hold calculated values.  The user user enters a date that a task was performed (TaskDate) and the following equation adds the correct date range to the Report Week field:

    CONCATENATE(TEXT(TaskDate - WEEKDAY(TaskDate) + 1, "mm/dd/yy", " - ", TEXT(TaskDate - WEEKDAY(TaskDate) + 7, "mm/dd/yy"))

    This is working perfectly.

    Ken

     
    • Marked as answer by Victoria Xia Thursday, October 2, 2014 7:42 AM
    Wednesday, October 1, 2014 8:47 PM