Mark days between two dates

[razaas]

I want to fill “X” in the cells that fall between to dates, suppose cell A1 has start date “Oct 1” and cell B1 has end date “Oct 10”, I want 10 cells (from C1 to L1) to be filled with “X” excluding weekend. Appreciate any help.

 

[Xiq]

Hi there,
Depending on how your sheet is build up (for my solution you need a date above for each column C:L), you could try this:

=IF(AND(C$1>=$A2,C$1<=$B2,WEEKDAY(C1,2)<6),"X","")

• Step 1: Have a date of the day at C1:L1
• Step 2: Place the given formula in C3 and copy to the right
• Step 3: ...
• Step 4: Profit?

 

[NARAYANK991]

Hi ,

Try this :

=IF(AND(WEEKDAY(INDEX(ROW(INDEX($A:$A,$A$1):INDEX($A:$A,$B$1)),COLUMN()-COLUMN($C:$C)+1))<>{1,7}),"X","")

Enter this as an array formula , using CTRL SHIFT ENTER , in C1 , and copy across.

Narayan

 

[Xiq]

Here is a slight modified solution if you DON'T want/have a date in row 1.
Place this in C1 and copy across

=IF(AND(($A1+COLUMN(A1)-1)>=$A1,($A1+COLUMN(A1)-1)<=$B1,WEEKDAY(($A1+COLUMN(A1)-1),2)<6),"X","")

Keep in mind: this will assume your first day is equal to the date in column A

 

[razaas]

Hi Xiq & Narayank991,

Thanks a lot, I chose the first formula and wanted to apologize for not able to thank promptly.

Thanks and have a great day.

Raza