Using a col of Dates by day I want to determine a wk ending date.

K

Ken Espo

Using a column of Dates by day I want to determine a wk ending date (sat
date) for each date, based on comparing that date to a range of dates that
are Sun thru Sat.
 
P

Peo Sjoblom

Assume the dates start in A1,

=A1+7-MOD(WEEKDAY(A1),8)

copy down as long as needed
 
M

Myrna Larson

Hi, Peo:

Why MOD(WEEKDAY(A1),8) rather than just WEEKDAY(A1)? For a series of dates,
Sun thru Sat, they both give the same answer.
 
P

Peo Sjoblom

Hi Myrna,

I know, but if the date is on a Saturday and you always want the next
Saturday it's easy
to change to =A1+7-MOD(WEEKDAY(A1),7)

if A1 holds 02/05/05 then the above will return 02/12/05 and I find it easy
to change

and you can't change =A1+7-WEEKDAY(A1) as easily
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to add day to PWA 2016 date format? 0
Date to Day 2
Sum amount of days in another coloumn 8
1st Wed, 1st Sat, 1st Sun 5
Conditional Formatting Problem - Dynamic Formula?? 2
Date Format 2
dates on worksheets 5
4 day work week 9

Top