I have a user that is trying to create a calculated field on a report. in this field she wants to convert a date to a week value. for example any date between 9/2/2018 to 9/8/2018 would convert to week 36. '36' being the number of weeks in the calendar year.
Has anyone used this before? If so can you let me know if This can be calculated or if a custom field is required?
Please sign in to leave a comment.
Week is one of the standard functions available within the workflows. Personally I would create a custom field to contain the week number of the date field you require and then include this in your report.
I am working on the custom field but the functions available are not as clear cut as I expected in order to create a conversion script. I am looking for direction on the coding syntax to be used.
Hello Kimberly,
The following formula should convert the Start Date of a work item into the week number according to the ISO-8601 standard:
'Week ' + ToString(If(ToOrganizationalDateTime(Date(Year($StartDate), Month($StartDate), Day($StartDate))) < Case(ToString(ToOrganizationalDateTime(Date(Year($StartDate), 1, 1)), 'ddd'), 'Mon', ToOrganizationalDateTime(Date(Year($StartDate), 1, 1)), 'Tue', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 12, 31)), 'Wed', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 12, 30)), 'Thu', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 12, 29)), 'Fri', ToOrganizationalDateTime(Date(Year($StartDate), 1, 4)), 'Sat', ToOrganizationalDateTime(Date(Year($StartDate), 1, 3)), ToOrganizationalDateTime(Date(Year($StartDate), 1, 2))), Truncate(DateDiff(ToOrganizationalDateTime(Date(Year($StartDate) - 1, 12, 31)), Case(ToString(ToOrganizationalDateTime(Date(Year($StartDate) - 1, 1, 1)), 'ddd'), 'Mon', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 1, 1)), 'Tue', ToOrganizationalDateTime(Date(Year($StartDate) - 2, 12, 31)), 'Wed', ToOrganizationalDateTime(Date(Year($StartDate) - 2, 12, 30)), 'Thu', ToOrganizationalDateTime(Date(Year($StartDate) - 2, 12, 29)), 'Fri', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 1, 4)), 'Sat', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 1, 3)), ToOrganizationalDateTime(Date(Year($StartDate) - 1, 1, 2))), 'd') / 7) + 1, If(ToOrganizationalDateTime(Date(Year($StartDate), Month($StartDate), Day($StartDate))) > Case(ToString(ToOrganizationalDateTime(Date(Year($StartDate), 12, 31)), 'ddd'), 'Mon', ToOrganizationalDateTime(Date(Year($StartDate), 12, 30)), 'Tue', ToOrganizationalDateTime(Date(Year($StartDate), 12, 29)), 'Wed', ToOrganizationalDateTime(Date(Year($StartDate), 12, 28)), 'Thu', ToOrganizationalDateTime(Date(Year($StartDate) + 1, 1, 3)),'Fri', ToOrganizationalDateTime(Date(Year($StartDate) + 1, 1, 2)), 'Sat', ToOrganizationalDateTime(Date(Year($StartDate) + 1, 1, 1)), ToOrganizationalDateTime(Date(Year($StartDate), 12, 31))), 1, Truncate(DateDiff(ToOrganizationalDateTime(Date(Year($StartDate), Month($StartDate), Day($StartDate))), Case(ToString(ToOrganizationalDateTime(Date(Year($StartDate), 1, 1)), 'ddd'), 'Mon', ToOrganizationalDateTime(Date(Year($StartDate), 1, 1)), 'Tue', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 12, 31)), 'Wed', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 12, 30)), 'Thu', ToOrganizationalDateTime(Date(Year($StartDate) - 1, 12, 29)), 'Fri', ToOrganizationalDateTime(Date(Year($StartDate), 1, 4)), 'Sat', ToOrganizationalDateTime(Date(Year($StartDate), 1, 3)), ToOrganizationalDateTime(Date(Year($StartDate), 1, 2))), 'd') / 7) + 1)))
ISO-8601 standard description:
Please let me know if this is helpful.
