0

I have a list grouped by the following formula.

 =CONCATENATE(TEXT([Effective Date]+(5-WEEKDAY([Effective Date])),"mm/dd/yyyy")," - ",TEXT([Effective Date]+(11-WEEKDAY([Effective Date])),"mm/dd/yyyy"))

Which gives something like this: Week : 04/30/2015 - 05/06/2015 (5)

But the items are not grouped properly based on the effective date. It does work correctly for some dates but others are messed up.

Is there an efficient way to group the items with the date range? The starting day of the week must be a Thursday and ending in Wednesday.

Edited: enter image description here

The formula works when the starting day is Monday and ending day is Friday. But seems not to work for Thursday - Wednesday.

  • I have edited the question. Added the screenshot. – Keerthi May 4 '15 at 14:00
  • The dates 4/26, 4/27, 4/28, 4/29 lies in the group 4/30/2015 - 5/6/2015. I need those dates to be under 4/23/2015 - 4/28/2015 – Keerthi May 4 '15 at 14:02
  • You have this in a calculated field? Can you add that field to the view and check ? – Amal Hashim May 4 '15 at 14:13
  • Yes, it is a calculated field. When I add it to the view, it shows the same date range. I think there is something wrong with the formula. It was working fine when I had the start of week to be Monday and end of week to be Friday. But when I changed it to Thursday - Wednesday, it is not working properly. – Keerthi May 4 '15 at 14:17
  • And as you can see in the screenshot, it is working fine for the second group, – Keerthi May 4 '15 at 14:19
1

Fixed it by the below formula:

=IF(WEEKDAY([Effective Date])>4,CONCATENATE(TEXT([Effective Date]+(5-WEEKDAY([Effective Date])),"mm/dd/yyyy")," - ",TEXT([Effective Date]+(11-WEEKDAY([Effective Date])),"mm/dd/yyyy")),CONCATENATE(TEXT([Effective Date]-(2+WEEKDAY([Effective Date])),"mm/dd/yyyy")," - ",TEXT([Effective Date]+(4-WEEKDAY([Effective Date])),"mm/dd/yyyy")))
  • Hi Keerthi what would be the formula if week starts on monday and ends on sunday? – mdevm Feb 24 at 19:41
0

Keerthi's solution didnt seem to work for me, so I modified theirs and used the following:

=CONCATENATE(TEXT([Work Date]-(WEEKDAY([Work Date],2)-1),"mm/dd/yyyy")," - ",TEXT([Work Date]+(7-WEEKDAY([Work Date],2)),"mm/dd/yyyy"))

Breakdown:

=CONCATENATE(
  TEXT([Work Date]-(WEEKDAY([Work Date],2)-1),"mm/dd/yyyy"),
  " - ",
  TEXT([Work Date]+(7-WEEKDAY([Work Date],2)),"mm/dd/yyyy")
)

Maybe this is different as im using Monday to Sunday dates? Either way, another option for people to explore if Keerthi's version doesn't work for them

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.