Excel, group, date, part of weekdays.

Question

I have a date and have to write down three to four weekday times every week. The week should be highlighted in colour. How can I achieve this? What is the best way to fill it in? Count up over the day.

Autofill.

The goal.

I'll use formulas now. The direction certainly fits. There are certainly a few optimisations that would be good. What needs to be considered with autofill? left mouse button right mouse button

• Increase from day
• Increment the month
• Increment the year

Answer 1

If what you want is to alternate the shading for successive weeks, then Conditional Formatting would seem a reasonable candidate. Two rules would be needed. One for when the date in question is an even number of weeks from the start and one for when it is an odd number or weeks.

Pick a date that is earlier than any date you would have to process AND that falls on the weekday that is the start of a week, for example 1 December 2024 is a Sunday. Compute the number of days between this start date and the date you are processing. Divide this number by 7 and discard the fractional part of the quotient. Format the cell based on whether this integer value is odd or even.

If you specify that the Conditional Formatting rule applies to cells E16:E35, then the even rule would look like =MOD(INT((E16-DATEVALUE("31/12/24"))/7),2)=0. The odd rule would replace =0 with =1.

You could put the constant start date value in a cell and replace the DATEVALUE function call with a reference to this cell. It is possible that BITAND would be more efficient than MOD.