date formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I create a formula that calculates the week ending date (date every Friday) so that it automatically appears according to the current date
For example the week ending date of this weeks pay period is 01-30-03 but if I open the spreadsheet on Monday 01-26, how do I get the week ending date to populate
 
=IF(WEEKDAY(A1)=5,A1,IF(WEEKDAY(A1)=7,A1+6,A1+6-WEEKDAY(A1)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

dj said:
How can I create a formula that calculates the week ending date (date
Click to expand...
every Friday) so that it automatically appears according to the current
date.
For example the week ending date of this weeks pay period is 01-30-03 but
Click to expand...
if I open the spreadsheet on Monday 01-26, how do I get the week ending date
to populate
 
One way:

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

or if you have a specific date in, say, A1:

= A1 - WEEKDAY(A1+1)+ 7
 
Back
Top