Read & set a start date on schedule and add weeks for monthly totals

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

Guest

Here's a tricky one
I need a formula that will not only read the date in cell D7 (which is easy), but then convert that date to be the first Sunday in the month for cell D7 (ie revert to the first Sunday in the month for whatever date entered into D7)

The next thing I'll do is (as a formula in the next cell) add 7 days to the prior weeks Sunday date and continue that formula to the right to make a schedule or Weeks - all starting on a Sunday that is generated from D7 which is the Campaign commencement date.

Lastly I need a formula sum's each months figures, except the formula has to work out whether it's a 5 week month or 4 week month? and include/exclude columns accordingly.

Would really appreciate any help on either parts of this problem.
 
Hi
to get the first Sunday of a month based on D7 try the following
formula
=DATE(YEAR(D7),MONTH(D7),1)+CHOOSE(WEEKDAY(DATE(YEAR(D7),MONTH(D7),1),1
),0,6,5,4,3,2,1)
then just simply add 7 to this result to get your week schedule

This will not convert your entry in D7 but requieres a separate cell to
store the date for the first Sunday

Frank
 
Hi,
I need a formula that will not only read the date in cell D7 (which is easy),
Click to expand...
but then convert that date to be the first Sunday in the month for cell D7 (ie
revert to the first Sunday in the month for whatever date entered into D7)
A formula to provide the first Sunday of the month of D7 date:

=D7-DAY(D7)+8-WEEKDAY(D7-DAY(D7))

The next thing I'll do is (as a formula in the next cell) add 7 days to the
Click to expand...
prior weeks Sunday date and continue that formula to the right to make a
schedule or Weeks - all starting on a Sunday that is generated from D7 which is
the Campaign commencement date.
Lastly I need a formula sum's each months figures, except the formula has to
Click to expand...
work out whether it's a 5 week month or 4 week month? and include/exclude
columns accordingly.

You can use the MONTH() function for that (if I understood correctly).

Regards,

Daniel M.
 
Back
Top