Formula to return the week ending date based on another cells date

cabeaudoin

Example: If cell A3 contains a date of 04/25/23, I'd like cell A10 to auto-populate a week-ending date of 04/28/23.

All the dates in cell A3 will occur sometime Monday thru Friday. The day in cell A10 will always be a Friday.

I've been searching community resources but haven't found this specific ask.

Danielle Arteaga

I figured this out using a helper column, which will hopefully work for you.

The Date column is set up as a date column. The Weekday Number and Weekday Name columns are text columns. You do not need the Weekday Name column - I used it just for testing - but I'll include it so you can test your own solution.

On row 3, in the Date column, type in 04/25/23.

On row 3 and row 10 of the Weekday Number column, place this formula:

=WEEKDAY(Date@row)

This returns a numeric value 1-7 that corresponds to a day of the week (where 1 = Sunday, 2 = Monday, etc.)

In the Weekday Name column on rows 3 and 10, place this formula:

=IF([Weekday Number]@row = 1, "Sunday", IF([Weekday Number]@row = 2, "Monday", IF([Weekday Number]@row = 3, "Tuesday", IF([Weekday Number]@row = 4, "Wednesday", IF([Weekday Number]@row = 5, "Thursday", IF([Weekday Number]@row = 6, "Friday", IF([Weekday Number]@row = 7, "Saturday")))))))

Finally, on row 10, in the Date column, place this formula:

=IF([Weekday Number]3 = 1, Date3 + 5, IF([Weekday Number]3 = 2, Date3 + 4, IF([Weekday Number]3 = 3, Date3 + 3, IF([Weekday Number]3 = 4, Date3 + 2, IF([Weekday Number]3 = 5, Date3 + 1, IF([Weekday Number]3 = 6, Date3, IF([Weekday Number]3 = 7, Date3 + 6)))))))

This formula adds the number of days to your date needed to make the WEEKDAY value add up to 6, which is the number assigned to Friday. You add 6 days to Saturday, because that gets you to the following (rather than previous) Friday.

You can hide the Weekday Number column on your sheet and delete the Weekday Name column altogether. (It's there just for visual validation your formula is working). 😀

cabeaudoin

Thank you for the time you put into this, no wonder I couldn't figure this out! I'll give this a try and report back.

Do you know if we can create helper columns on another worksheet that links out? My sheet has so many helper cells/columns already and this is a shared sheet. I'd like to eliminate having them visible to others so they have a more targeted and condensed user experience.

cabeaudoin

UPDATE: Got this to work with a helper column within the same sheet.

I'm not a fan of helper columns at all because my worksheets end up being so large to begin with and it's visually distracting to our end-users. While we can hide columns it would be nice if we could select which columns we want to unhide when needed; it's currently all or nothing. Also, if we could hide helper columns separately from other column data, that would be extra helpful. 😉