Given a date, determine a week ending date

[DashboardNovice]

In the attached file, I have timesheet dates in column C. Columns G and H define when each work week begins (always on a Monday) and when it ends (always on a Sunday).

For any given date in column C, I want to determine what the week ending date is.

Can I do this w/o using VBA loops?

 

[NARAYANK991]

Hi ,

Given a date , subtracting its WEEKDAY will always return the previous Saturday.

=Given_Date-WEEKDAY(Given_Date)

So if you want the Sunday , just add 1 to the resulting date.

Narayan

 

[chssundeep]

Narayan as always your solution is great

As per my understanding he is looking for the following week Sunday....if that is the case.....Just adding few lines to your solution....

=(C5+1-WEEKDAY(C5))+7

 

[DashboardNovice]

Narayan as always your solution is great

As per my understanding he is looking for the following week Sunday....if that is the case.....Just adding few lines to your solution....

=(C5+1-WEEKDAY(C5))+7

I just realized that this doesn't work if the last day is on a Sunday itself. For example 8/7, 8/8, 8/9 gives 8/10 for the week ending date of Sunday which is correct.

But 8/10 should give 8/10 since it is on a Sunday, but instead it gives 8/17. I can't figure out how to fix this.

Basically, for any given day of the week, round up to the next Sunday. If the day of the week is Sunday, then the result should be that same day.

 

[Haseeb A]

One way:

=C4+7-WEEKDAY(C4,2)

 

[DashboardNovice]

One way:

=C4+7-WEEKDAY(C4,2)

Just what I was looking for. I really appreciate your reply.