Current Monday and future Sunday.

  • Thread starter Thread starter MitchellWMA
  • Start date Start date
M

MitchellWMA

I google the archives and came up with this for the Monday of the
current week, i.e., I start the work week on Mondays and that's how my
time sheets operate. So today being the first day of the work week
which ends the following Sunday, here is what I found for Monday of
the current week:

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

So today the time sheet shows Dec.15 for today, Monday.

How can I get next Sunday's date formulaically, which would be Dec.21?

Thanks! :)
 
=cellref + 6

Where cellref is the cell with your WEEKDAY formula.


Gord Dibben MS Excel MVP
 
How about:
=8-WEEKDAY(TODAY())+TODAY()
Click to expand...

So that's how it works. I'm guessing that 2 is for Monday since it
actually is the 2nd day of the week, and 8 makes the following Sunday,
which would otherwise be #1 of the following week, the "last" day of
the week as far as my time sheet goes. They sent us the time sheet in
Excel so much easier to just put the formulas in and print out anytime
during the weekend before Friday afternoon.

I _was_ currently using the formula =J26 for Sunday as Sunday is shown
on the sheet, but it's much better to have a separate formula in this
case. Too many other parts of the formula are based on adding "1" to
the cell above it for the right date for each day of the week.

Thanks. :)
 
Forgot to update this thread. Thought would be a good idea to list
code for all days of the week:

Sunday of this current week: =1-WEEKDAY(TODAY())+TODAY()
Monday of this current week: =2-WEEKDAY(TODAY())+TODAY()
Tuesday of this current week: =3-WEEKDAY(TODAY())+TODAY()
Wednesday of this current week: =4-WEEKDAY(TODAY())+TODAY()
Thursday of this current week: =5-WEEKDAY(TODAY())+TODAY()
Friday of this current week: =6-WEEKDAY(TODAY())+TODAY()
Saturday of this current week: =7-WEEKDAY(TODAY())+TODAY()
SUNDAY of following week, when
Monday & not Sunday is used
as day #1 of week: =8-WEEKDAY(TODAY())+TODAY()

Thx. <g>
 
Please keep your threads together, stuff like this if very difficult to
follow and a waste of our time.

Thx <s>
 
Please keep your threads together, stuff like this if very difficult to
follow and a waste of our time.

Thx <s>
Click to expand...

Pls be careful of what you're talking about. This thread _is_ all
together.
When a criticism is warranted, I'll take it. I won't when it is not.
 
Back
Top