time calculation, find next sunday?

  • Thread starter Thread starter ToddL
  • Start date Start date
T

ToddL

Hi, I am working with a payroll timesheet and the formulas
key off the weekending date. The week always ends on
Sunday and I am wondering if I can use the now() formula
(or somehow) and get the next Sunday to auto calc. So the
users don't have to ask me what date is next Sunday a
hundred times in a day. :) Any help at all would be
appreciated.

TIA


Todd
 
Hi Todd

according to my calcs this should return the date of the next sunday ..

=NOW()+(8-WEEKDAY(NOW(),1))

Cheers
JulieD
 
Hi, I am working with a payroll timesheet and the formulas
key off the weekending date. The week always ends on
Sunday and I am wondering if I can use the now() formula
(or somehow) and get the next Sunday to auto calc. So the
users don't have to ask me what date is next Sunday a
hundred times in a day. :) Any help at all would be
appreciated.

TIA


Todd
Click to expand...

It depends on what you want to do if TODAY is a Sunday.

If you want the next Sunday, then:

=today()-WEEKDAY(today())+8

If you want to stay on the same date, then:

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


--ron
 
First, don't use NOW(): NOW() includes the time of day, so if you have
something like:

=IF(NOW()=DATE(2004,8,22), "This Sunday", "Some other day")

will only be true exactly at 00:00 (12:00 am) Sunday, 22 August 2004.

Instead use the TODAY() function.

Since Sunday is the end of the week, I'll assume that on Sunday, 22
August 2004, the formula's result should be 22 August 2004:

=TODAY()-WEEKDAY(TODAY(),2)+7

If instead the result should be 29 August 2004, use

=TODAY()-WEEKDAY(TODAY(),1)+8
 
Back
Top