Share via

Formula for date to equal Friday or day before the end of the month if the quarter changes

Anonymous
2022-09-19T16:52:26+00:00

I am making a system based on the current quarter (i.e., 13 weeks per quarter, 4 quarters per year). As you see in the above graph I have cell G4 = the first day of the year, and cell G5 = the first Friday of the year. I am trying to figure out the formula for this so that if the ending day of the month isn't Friday, the cells underneath will still be back on track for Fridays. You can see that week 13 = 4/1/22, but quarter 1 should end in March and not April.

To simplify, I just want 13 consecutive weeks to end on Friday until week 13 when it is either Friday or the last day of that month. Then from week 14-25, it is on Friday, and on week 26, it is again either Friday or the last day of that month. The goal is to have 13 weeks in the quarter with the work week ending on Fridays.

Thanks!

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2022-09-19T19:59:44+00:00

    You are very close! There was a couple things that didn't come up quite right. The first is that when I looked to next year, 9/29/23 and 9/30 are both registering, when I only want to have 9/29/23 and then go to 10/6 because it is the next Friday.

    This is the only other thing I noticed. When I jumped to 2024, This is also doing what was seen in the example right above. It is registering the last day of the month, when I only need it if there is a Friday in the week as well. For example, March 2022 ends on the 31st, which is a Thursday. What I would like here is for it to have the 13th week (last week in the quarter) end on that Thursday, and then have the next week be April 8, 2022.

    To clarify, I want 13 weeks in the quarter and on the last week I would like it to end on the last day of the month if there is a friday in that week. This way there will be 13 weeks and the next week will begin on the next Friday. I am prioritizing it on fridays unless the end of the month on the last week of the given quarter comes first.

    Again, really appreciate your help as you are very close!

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2022-09-19T18:08:07+00:00

    EYF613,

    For reference cell "1/1/22" is cell G4 and "1/7/22" is cell G5.

    When I put the above formula into cell G5 with the previous date "1/7/22", it now shows 1/8/22, which is a Saturday and I need it to be on Fridays.

    The other thing is that for week 13, "3/31/22" is exactly what I was looking for, but then I wanted week 14 to be "4/8/22" (the next Friday since this week is already represented with "3/31/22". So, the last day of the quarter is correct, but just need to fix it to be on Fridays and not Saturday, and then once it uses the last day of the month instead of Friday since it is in the next quarter, then it uses the next Friday's date.

    Thanks for your help on this!

    Was this answer helpful?

    0 comments
  5. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more