Weekly Calendar

[Ben]

I'd like to have Excel give me the dates for Monday-Friday each week in
columns D4-H4 and update this once weekly, I guess preferably on
Sunday. I've just started thinking about it, and I wondered if anyone
has already worked something like this out.

 

[Ivyleaf]

I'd like to have Excel give me the dates for Monday-Friday each week in
columns D4-H4 and update this once weekly, I guess preferably on
Sunday. I've just started thinking about it, and I wondered if anyone
has already worked something like this out.

Hi Ben,

Not sure if this is exactly what you want, but you could try the
following:

In cell A1, enter the formula "=now()-weekday(now(),3)" - This will
give the date for Monday in the current week.
In cell B1 enter the formula "=a1+1", then fill this across for the
required week.

With this in place, the sheet will always show the dates of the current
week and will update automatically.

* Remember to format the cells as a date.

Cheers,
Ivan.

 

[Ben]

Hi Ben,

Not sure if this is exactly what you want, but you could try the
following:

In cell A1, enter the formula "=now()-weekday(now(),3)" - This will
give the date for Monday in the current week.
In cell B1 enter the formula "=a1+1", then fill this across for the
required week.

With this in place, the sheet will always show the dates of the current
week and will update automatically.

* Remember to format the cells as a date.

Cheers,
Ivan.

Thanks for the reply. I ended up using
=TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,-1,-2,-3,-4,-5), which worked fine
even though it's more complicated than your elegant formula.

When I tried yours, it correctly identified the Monday-Friday dates
throughout the week, but the conditional formatting for each day that
was based on "today()" didn't work. Any idea why?

 

[Ivyleaf]

=TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,-1,-2,-3,-4,-5), which worked fine
even though it's more complicated than your elegant formula.

When I tried yours, it correctly identified the Monday-Friday dates
throughout the week, but the conditional formatting for each day that
was based on "today()" didn't work. Any idea why?- Hide quoted text -- Show quoted text -

Hi Ben,

Had a quick look at it... I reckon it is because the now() function
includes the time in the serial number. I changed now() to today() in
all the formulas, and it fixed the problem. ie.
"=TODAY()-WEEKDAY(TODAY(),3)" in the first cell then "=A1=TODAY()" in
the conditional formatting.
Still, good to hear you got it working anyway.

Kind Regards,
Ivan.

 

[Ben]

Try putting =today() in cell a1 then the formula =A1-MOD(A1,7)+2 in d4.
In h4 you can just have d4+1 etc that will always work.